Querying for N records associated with each record referenced by set of record keys.

Question

Hello,

Here's a simplified version of my scenario:

• I have a table named Project that I reference via an 'id' field.
• I have a table named Photo that has a field named 'project_id' which I use to relate multiple photos to a single project. The Photo table also has an auto-incrementing 'id' field that I use for ordering, amongst other things.

Here's what I want to accomplish: For a collection of Project id values, I wish to retrieve the last 5 photos added to each project -- ideally in a single query, of course. :-)

Put another way, instead of imposing a single query limit, I would ideally like to specify a per project limit on the number of photos returned.

I am currently implementing this as one-query per project, so N projects = N queries (a good caching strategy will certainly reduce the hurt, but that'll come later).

Anyone have a solution?

Thanks.

Answer 1

For the "last n items" problem in MySQL, take look here: How to select maximum 3 items per users in MySQL? (it's right in the top answer).

When you take it from there, all you are missing is a JOIN with your Projects table, which should be easy to do.

Answer 2

SELECT project.*, photo.* 
  FROM photo 
  LEFT JOIN project USING project_id 
WHERE photo.project_id = '{$id}' 
ORDER BY photo_id DESC 
LIMIT project.project_photos_limit

Try that? (with your fieldnames, obviously)

Answer 3

A slight alteration of philistyne's solution:

SELECT project.*, photo.* 
  FROM photo 
  LEFT JOIN project USING(project_id)
WHERE photo.project_id IN (comma separated list of project ids)
ORDER BY photo_id DESC 
LIMIT project.project_photos_limit

Although I'm not sure if LIMIT will let you use a field to do that.