Categories
Development

Return mixed non-series items + next upcoming in series

I’m looking for the best way to handle the following query. I have a table of items, some (but not all) of which are part of a series of items (one per day). The ones in a series are grouped by a shared series ID:

Table "items":
    id
    series_id (may be NULL)
    title
    date
    etc.

The difficulty arises when I sometimes only want to return the next upcoming instance of any items which are part of a series. Something like:

SELECT items.id, items.series_id, items.title, items.date
FROM items
LEFT JOIN
    items AS series_table
    ON series_table.series_id IS NOT NULL
      AND series_table.series_id=items.series_id
      AND series_table.date>=CURDATE()
WHERE items.date>=CURDATE()
GROUP BY
    items.id 
HAVING
    (items.series_id IS NULL OR items.date=MIN(series_table.date));

This seems to be inefficient. Doing it with a subquery in the WHERE clause also doesn’t perform that well on my data set. Is there a better way to do this? I realize it might make sense to perform two queries with a UNION but unfortunately that’s the one thing that the application executing this query would make difficult, so I’m hoping there’s a better single-query approach.

Sample data:

CREATE TABLE items (
    id int(11) unsigned NOT NULL AUTO_INCREMENT, 
    title varchar(30) NOT NULL DEFAULT '', 
    series_id int(10) DEFAULT NULL, 
    date datetime NOT NULL, 
    PRIMARY KEY (id), 
    KEY series_id (series_id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO items (id, title, series_id, date)
   VALUES (1, 'Item 1 (no series)', NULL, '2019-12-01 00:00:00'),
          (2, 'Item 2 (no series)', NULL, '2019-12-02 00:00:00'),
          (3, 'Item 3 (series)', 1, '2019-12-03 00:00:00'),
          (4, 'Item 4 (series)', 1, '2019-12-04 00:00:00');

Leave a Reply

Your email address will not be published. Required fields are marked *