SQL_CALC_FOUND_ROWS on a subquery?

OK I know SQL_CALC_FOUND_ROWS can’t be used in a subquery, but I it’s the kind of functionality I’m after, and I can’t figure out how best to re-arrange my query.

Let’s say I’ve got two tables, Articles, and ArticleComments. I want to list the articles and comments and paginate in such a way that each page shows 50 articles, and their comments, however many that may be. So page 1 would display articles 1-50, along with all of the comments for those articles. So the normal SQL_CALC_FOUND_ROWS/LIMIT 0,50 wouldn’t give me the first 50 articles, it would give me the first 50 rows of article/articleComment.

An approach like this would be great:


SELECT
a.*,
ac.commentId, ac.comment
(
	SELECT SQL_CALC_FOUND_ROWS a.articleId, a.articleName
	FROM
		Articles a
		WHERE a.submissionDate>"2013-01-01" #or some other arbitrary filter
	LIMIT 0,50
) a
LEFT JOIN
	ArticleComments ac
	ON a.articleId = ac.articleId

But of course you can’t apply SQL_CALC_FOUND_ROWS to the subquery.

Using GROUP_CONCAT such that my results bunch all the comments for an article into one row would work OK, if the results weren’t textual, and likely to be truncated.

Or a few approaches which use multiple queries:
I could run a query to get the article list for a given page, and another to get the comments for a list of articleId’s. That’s 2 queries for each page.
I could run an initial COUNT query to work out the total articles for the given filter, and cache that so as not to have to run this on each page. That’s 2 queries on the first page and 1 query on subsequent pages.

None of these solutions seem to have the elegant simplicity that SQL_CALC_FOUND_ROWS usually presents.

Any other solutions you can think of?

this is exactly how i would do it

make sure there’s an index on every column used in the first query (in your example, submissionDate) and return the 50 article ids

then use those 50 article ids in an IN list in the 2nd query

easy peasy

sometimes, 2 simple queries instead of 1 convoluted query is da bomb :wink:

True dat. Sometimes it seems the first 25% of my learning databases was figuring out cool ways to cram everything into one query. The remaining 85% is learning not to.