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,
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 SQL_CALC_FOUND_ROWS a.articleId, a.articleName
WHERE a.submissionDate>"2013-01-01" #or some other arbitrary filter
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?