Table relations image is in this link http://www.serofero.net/mvb-table-relation.png
I am using php as server side programming language and MySQL as database.
Problem Description
User adds a new venue. One venue may have multiple beverages, events, features so on and so forth. Now , I want such a query or magic so that I could collect all the beverages, events, features, foods, styles, types, event_options and space_requirements related to each venue along with its venue_id, name, description, capacity, min_rate, max_rate, location (from locations table). Also I need to offset and limit the result so that I could implement pagination in backend. But the challange is that the limit should limit the number of venues not its beverages, foods, styles etc.
I am also wondering to collect the result in php array as below:
$result = array(
0=> array(
"name" => "Venue A",
"description" => "Venue A description",
"capacity" => "Venue A capacity",
"location" => "Venue A location",
"beverages" => array('beverage1','beverage23','beverage7',...),
"events" => array('event8','event17','event19','event4',...),
"features" => array('features1',...),
"foods" => array(),
"styles" => array(),
"types" => array('type7', 'type14', 'type23',...),
"event_options" => array(),
"space_requirements" => array()
)
,
1=> array(
"name" => "Venue B",
"description" => "Venue B description",
"capacity" => "Venue B capacity",
"location" => "Venue B location",
"beverages" => array('beverage1'),
"events" => array('event2','event7','event9','event4',...),
"features" => array(),
"foods" => array(),
"styles" => array('style1', 'style2',...),
"types" => array('type47', 'type4', 'type3',...),
"event_options" => array(),
"space_requirements" => array()
)
);
Today is 5th day I am trying to figure out the solution but I have been failed all the time. Below is the snippet of MySQL Query that I could write till now.
SELECT v.name, e.event, t.type, s.style
FROM venues v
LEFT JOIN venue_events ve ON v.venue_id = ve.venue_id
LEFT JOIN events e ON e.event_id = ve.event_id
LEFT JOIN venue_types vt ON v.venue_id = vt.venue_id
LEFT JOIN types t ON t.type_id = vt.type_id
LEFT JOIN venue_styles vs ON v.venue_id = vs.venue_id
LEFT JOIN styles s ON s.style_id = vs.style_id
WHERE v.venue_id IN (SELECT venue_id FROM venues) LIMIT 0,5
/* I want to limit the number of "venues" but the LIMIT 0,5 limits the number of 'events', 'types' , 'styles' the "venue" have. And this is the main problem.
I have also tried :
WHERE v.venue_id IN (SELECT venue_id FROM venues LIMIT 0,5) but it raises the MySQL error.
*/
But I dont know what to do next to get the result as I mentioned above.
Please help me.
Thankyou.
SELECT DISTINCT ven.venue_id, ven.name, e.event_id, e.event, t.type_id, t.type, s.style_id, s.style
FROM (SELECT * FROM venues v LIMIT 0,5) ven /*This line does magic for me*/
LEFT JOIN venue_events ve ON ven.venue_id = ve.venue_id
LEFT JOIN events e ON e.event_id = ve.event_id
LEFT JOIN venue_types vt ON ven.venue_id = vt.venue_id
LEFT JOIN types t ON t.type_id = vt.type_id
LEFT JOIN venue_styles vs ON ven.venue_id = vs.venue_id
LEFT JOIN styles s ON s.style_id = vs.style_id