Clever Geek Handbook

Query for N records associated with each record referenced by a set of record keys

Here's a simplified version of my script:

  • I have a table named Project which I link to via the "id" field.
  • I have a table named Photos that has a field named "project_id" that I use to link multiple photos to one project. The Photos table also has an auto-incrementing "id" field that I use, for example, to organize.

Here is what I want to accomplish: For a collection of project ID values, I want to get the last 5 photos added to each project, ideally in one request, of course. :-)

In other words, instead of imposing a single request limit, I would ideally like to specify a per project limit on the number of photos returned.

I am currently implementing this as one request per project, so N projects = N requests (a good caching strategy will certainly lessen the pain, but that will come later).

Anyone have a solution?

Thank.

+1


source to share


3 answers


For the "last n items" problem in MySQL, take a look here: How to select a maximum of 3 items per user in MySQL? (this is correct in the top answer).



When you take it from there, all you are missing is a JOIN with your table Projects

, which should be easy to do.

+1


source 


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 it? (with your field names obviously)

0


source


A small change to the philistine 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

Though I'm not sure if LIMIT will allow you to use a field for this.

0


source






More articles:


All Articles