I have two tables with a one to many relationship:
collection: id, user_id, title, description, item_count
items: id, collection_id, title, description
Now, I want to display the collections on a result page. (paginated). But, for each collection, I want to show the first 3 or so items that are in it.
What is the best possible way to do this efficiently? If I just do a straight up join I get the collection information 3 times as well, which I do not need.
Or should I just do a second query after having queried for the collections first? (Then using the collection_ids to get the items)?
What I do now:
SELECT c.id, c.title, c.description, GROUP_CONCAT(i.id) AS item_ids FROM collections c INNER JOIN items i ON i.collection_id = c.id ORDER by c.created_at DESC LIMIT 10
Then i collect those item_ids in php and do a “WHERE id IN (…)” on all those ids.
But I need to know if there is a better solution.
Thank you for your help,