2

I have two tables:

EVENTS

ID Name
1 First event
2 Second event
3 Third event
4 Fourth event
5 Fifth event

EVENTS_META

ID EventID MetaKey MetaValue
1 2 date_expired 2023-02-09 00:00:00
2 2 date_expired 2023-01-23 00:00:00
3 3 date_expired 2023-04-30 00:00:00
4 3 date_expired 2023-01-22 00:00:00
5 4 date 2023-06-12 00:00:00
6 4 date 2023-02-03 00:00:00
7 5 date_expired 2023-02-13 00:00:00
8 5 date 2023-01-02 00:00:00
9 5 date 2023-01-01 00:00:00

I would like to select all items from events table ordered by the following logic:

  1. First "group" of items on the list are the ones that have any "date" rows in events_meta table.
  2. Second "group" of items on the list are the ones that have any "date_expired" rows in events_meta table.
  3. Third "group" of items on the list are the ones that don't have "date" nor "date_expired" rows.

Also, within each of those groups (except the last one) events are ordered by their "date" or "date_expired" minimum date values. So the first group is ordered by "date" dates. Second group is ordered by "date_expired" dates.

Each event can have any number of "date" or "date_expired" rows attached to them.

The desired outcome would be:

  1. Fifth event
  2. Fourth event
  3. Third event
  4. Second event
  5. First event

I think I have managed to create the "groups" but I'm struggling to sort the groups by dates:

SELECT events.Name FROM `events` LEFT JOIN `events_meta` ON events.ID = events_meta.EventId 
GROUP BY events.Name
ORDER BY 
    CASE WHEN (SELECT COUNT(*) FROM events_meta WHERE events.ID = events_meta.EventID AND events_meta.MetaKey = 'date') > 0 THEN 0
    WHEN (SELECT COUNT(*) FROM events_meta WHERE events.ID = events_meta.EventID AND events_meta.MetaKey = 'date_expired' AND events_meta.MetaKey != 'date') > 0 THEN 1
    ELSE 2
    END;

Here's a dbfiddle with the exact same dataset: https://dbfiddle.uk/55vp__vZ

1 Answer 1

1
SELECT events.Name
FROM events
LEFT JOIN events_meta ON events.ID = events_meta.EventId
GROUP BY 1
ORDER BY MIN(CASE WHEN events_meta.MetaKey = 'date'
                  THEN events_meta.MetaValue 
                  ELSE '9999-12-31'
                  END),
         MIN(CASE WHEN events_meta.MetaKey = 'date_expired' 
                  THEN events_meta.MetaValue 
                  ELSE '9999-12-31' 
                  END)

fiddle.

1
  • Thanks, this is exactly what I was looking for. Appreaciate the breakdown in the fiddle as well, much easier to understand.
    – Martin J
    Apr 1, 2023 at 14:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.