One to many -- Select 3 subitems for every item.

  • Filter
  • Time
  • Show
Clear All
new posts

  • One to many -- Select 3 subitems for every item.


    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,

  • #2
    I think that is what I would start with. Pagination is often a serious performance problem, so full results can be very inefficient.