Say I have a table that represents events, with a primary event_ID key. Data might be event_name, event_location, event_date.
Then I have a table that represents attendees, with a primary attendee_ID key. Data might be first_name, last_name, contact_number
I have a link table which uses the event_ID and attendee_ID as foreign keys.
Now I need to query this. I want to return a record set of events between two given dates. I also need to look up the attendees of each event.
I'll be using PHP to map these records to an object. What I'll be expecting is something like:
array(
// Multiple `events`
array(
'event_ID' => 1,
'event_location' => 'London',
'event_start' => '2014-11-27',
'attendees' => array(
// multiple `attendees`
array(
'attendee_ID' => 1,
'first_name' => 'John'
'last_name' => 'Smith',
'contact_number' => '0207 123 1234',
),
...
),
),
...
);
I have tried Googling this, but cannot think of how to express what I need to achieve in a search term. I guess I need to Query heirachical data.
What I don't want to do is get a record set of events, then iterate through them making more DB calls to populate the attendees, as this seems horribly inefficient.
Another idea I was think of was to get all event records, then query for the link table, collate the attendee IDs and query the attendee table for any that match the meetings and use PHP code to re-associate them with my event object I built from the recordset.
Is there a more elegant, best practice way of achieving this?
I assume that you have a column on attendees table for specifying which event user attended. This is called attended_event_ID. By using this, you can do that with following;
<?php
$query = "SELECT e.event_ID, e.event_location, e.event_start,
a.attendee_ID, a.first_name, a.last_name, a.contact_number, a.attended_event_ID
FROM events e
LEFT JOIN attendees a ON a.attended_event_ID = e.event_ID
ORDER BY e.event_ID";
$result = $db->query($query);
while($row = $result->fetch_assoc()){
$events[$row["event_ID"]]["event_ID"] = $row["event_ID"];
$events[$row["event_ID"]]["event_location"] = $row["event_location"];
$events[$row["event_ID"]]["event_start"] = $row["event_start"];
$events[$row["event_ID"]]["attendees"][] = array(
"attendee_ID" => $row["attendee_ID"],
"first_name" => $row["first_name"],
"last_name" => $row["last_name"],
"contact_number" => $row["contact_number"]
);
}
?>
select
event . *,
CONCAT('[',
GROUP_CONCAT(CONCAT('{"name":"',
attendees.name,
'", id:"',
attendees.id,
'"}')),
']') attendees
FROM
`event`
LEFT JOIN
attendees ON attendees.event = event.id
GROUP BY event.id
Will return your results in the following format
1 event2 [{"name":"name-1-1", id:"6142"},{"name":"name-1-1", id:"2048"},{"name":"name-1-1", id:"1"},{"name":"name-1-1", id:"4095"}]
2 event3 [{"name":"name-2-2", id:"2"},{"name":"name-2-2", id:"4096"},{"name":"name-2-2", id:"6143"},{"name":"name-2-2", id:"2049"}]
3 event4 [{"name":"name-3-3", id:"6144"},{"name":"name-3-3", id:"2050"},{"name":"name-3-3", id:"3"},{"name":"name-3-3", id:"4097"}]
4 event5 [{"name":"name-4-4", id:"4"},{"name":"name-4-4", id:"4098"},{"name":"name-4-4", id:"6145"},{"name":"name-4-4", id:"2051"}]
5 event6 [{"name":"name-5-5", id:"6146"},{"name":"name-5-5", id:"2052"},{"name":"name-5-5", id:"5"},{"name":"name-5-5", id:"4099"}]
6 event7 [{"name":"name-6-6", id:"6"},{"name":"name-6-6", id:"4100"},{"name":"name-6-6", id:"6147"},{"name":"name-6-6", id:"2053"}]
The attendees result is a valid JSON and can be decoded using JSON_DECODE.
Make sure you have indexes This works in 0.047 seconds on 2000+ events and 8000+ attendees