As you can see in this sql-fiddle, I have three tables:
The original tables contain various other fields, which I omitted as they aren't relevant, I just need to have them in my final result, but I do no operation/sort/filtering/grouping on them.
An event has 1+
event_dates and an event_date has 0+
From an object point of view, an event object has a list of event_date objects, which in turn can have a list of event_time objects.
My final goal is to obtain an array of event objects.
My plan is to design a query returning the ids of matching records and from these results fetch the records from their tables in 3 queries and assemble everything in code (this is necessary because of various data-transformations outside the scope of SQL).
My problem is getting the first query right
At the moment I have the following query
SELECT events.id, CONCAT('[', GROUP_CONCAT(event_dates.id SEPARATOR ','), ']') AS event_date_ids, CONCAT('[', COALESCE(GROUP_CONCAT(event_times_per_event_date.event_time_ids SEPARATOR ','), ''), ']') AS event_time_ids FROM events INNER JOIN event_dates ON event_dates.event_id = events.id LEFT JOIN ( SELECT date_id AS event_date_id, CONCAT('[', COALESCE(GROUP_CONCAT(event_times.id SEPARATOR ','), ''), ']') AS event_time_ids FROM event_dates LEFT JOIN event_times ON event_times.date_id = event_dates.id GROUP BY event_dates.id ) AS event_times_per_event_date ON event_times_per_event_date.event_date_id = event_dates.id GROUP BY events.id
which fails in the following situation:
Given event e with three dates d1,d2 and d3, where d1 has two times (t1,t2), d2 has zero times, d3 has two times(t3,t4). The result of the query would be:
event_id | event_date_ids | event_time_ids 1 | [1,2,3] | [[1,2],[3,4]]
What I'd expect would be an empty array in event_time_ids, so that I could match the indices of the array in event_date_ids with the one for the array in event_time_ids:
event_id | event_date_ids | event_time_ids 1 | [1,2,3] | [[1,2], , [3,4]]
How can I obtain this?
P.S.: I deeply apologise if my question is chaotic or unclear and I'm ready to provide every other necessary information.
Thanks a lot to @Andriy M for spotting my mistake.
Moving on, I have to add to the equation
event_categories where events have 1+ categories.
DISTINCT in the
GROUP_CONCAT would break my current solution as empty arrays would be removed.
The solution is to make this query into a subquery, and join its results to a query fetching all other event data and grouping by
The event_time_ids would be duplicate for every event row, thus selecting any row for that column is fine.