Get the first 25 articles with their images with subqueries and limit - funky pagination

[hessodreamy]

Say you wanted a paginated list of articles (25 per page), but you also wanted to get their images (with some conditions), and each article might have multiple images. Using the basic LIMIT would return eg the first 25 ROWS, but not the first 25 articles:

SELECT article.id, article.name, image.id, image.file FROM article JOIN image ON article.id = image.article_id WHERE article.status = 1 AND image.status=1 LIMIT 25

So I started running the limit on a subquery:

SELECT article.id, article.name, image.id, image.file FROM (SELECT article.id, article.name FROM article WHERE article.status = 1 LIMIT 25) AS article JOIN image ON article.id = image.article_id WHERE image.status = 1

However, the presence of a given article in the results also depends on it having an image with status=1, so it’s possible to have a page of less than 25 articles, although the are more articles on the next page.

The only way I can think of to solve this in sql is to run the entire query in a subquery with a GROUP AND a limit, and then re-do the join on image:

SELECT article.id, article.name, image.id, image.file FROM (SELECT article.id, article.name FROM article JOIN image ON article.id = image.article_id WHERE article.status = 1 AND image.status=1 GROUP BY article.id LIMIT 25) AS article JOIN image ON article.id = image.article_id WHERE image.status=1

This gives me the first 25 articles, and the correct images. It might be more than 25 rows in all, but it’s 25 articles.

Another approach would be to just return all the rows and iterate in the application. I’m not a fan of that approach.

Any more elegant solutions I’ve missed? I’m using MariaDB and PHP.

[molona]

I would think that your first query would work if each article only has 1 image with status 1. But since it doesn’t work, I’m assuming that you don’t.

If you have more than one image for each article with status 1… then, how do you choose which image you should use? Will you choose the first image with that status? the last?

Probably that could lead you to an approach where you can avoid subqueries, I think.

[hessodreamy]

To clarify, I want all the images that go with the selected articles, and there will be many for each article.

[r937]

@hessodreamy

Any more elegant solutions I’ve missed? I’m using MariaDB and PHP.

run one query to retrieve your 25 articles

SELECT id , name FROM article WHERE status = 1 ORDER BY something_please_for_gods_sake LIMIT 25

then use the retrieved article ids to retrieve images

SELECT id , article_id , file FROM image WHERE article_id IN ( list , of , ids , from , first , query ) AND status = 1

finally, “join” images to articles with php array wizardry ™

[DaveMaxwell]

Edit: Rudy’s of course has the simpler approach, but since I started the post…

A good rule of thumb to use (though there are exceptions) is to put conditions on joined tables into the ON condition, and the conditions on the primary table in the WHERE clause. So this version of your first query SHOULD get you closer to what you’re looking for.

SELECT article.id
     , article.name
     , image.id
     , image.file
  FROM article
  JOIN image ON article.id = image.article_id AND image_status = 1
 WHERE article.status = 1
 LIMIT 25

That’ll hose you if you have more than one image with image_status = 1, but you can handle that with a sub-queried join. Depends on if you need it or not…

SELECT article.id
     , article.name
     , image.id
     , image.file
  FROM article
  JOIN (SELECT article_id
             , MIN(id) as imageID
          FROM image
         WHERE image_status = 1
         GROUP BY article_id) i ON article.id = i.article_id AND image_status = 1
  JOIN image ON image.id = i.imageID
 WHERE article.status = 1
 LIMIT 25

[hessodreamy]

@r937

ORDER
BY something_please_for_gods_sake LIMIT 25[/code]

Fair point. In my defence this is a simplified situation for the sake of demonstration.

@r937

run one query to retrieve your 25 articles

SELECT id , name FROM article WHERE status = 1 ORDER BY something_please_for_gods_sake LIMIT 25

then use the retrieved article ids to retrieve images

SELECT id , article_id , file FROM image WHERE article_id IN ( list , of , ids , from , first , query ) AND status = 1

finally, “join” images to articles with php array wizardry ™

To clarify, I’m only looking for articles which have at least one image with status 1, so that initial query would need the image join and its condition, even if I’m only getting the articles:

SELECT article.id
     , article.name
  FROM article
JOIN image ON article.id = image.article_id AND image.status=1
 WHERE article.status = 1 
ORDER
    BY something_please_for_gods_sake LIMIT 25

But sure, that’s a different approach. Is it likely to be more or less efficient than combining them into one query? I’m not sure, I feel some benchmarking coming on!

[r937]

@hessodreamy

Is it likely to be more or less efficient than combining them into one query?

yes!!!

[hessodreamy]

@r937

@hessodreamy

Is it likely to be more or less efficient than combining them into one query?

yes!!!

I tried one way then the other. Turned off query caching and ran 1000 iterations with a single query, then 1000 iterations with two queries, and there was no real difference between the two approaches. And that’s not counting the time/effort involved in php combining the result sets.