CODEWITHSUNDEEP
mysqlsql
Scarface
Scarface

Reputation: 3913

Is it possible to use count to get total rows while maintaining query?

The following query gets all the results from a table called video

SELECT video.* from video 
LEFT JOIN conv_progress on video.vid_id=conv_progress.vid_id 
LEFT JOIN upload_progress on video.vid_id=upload_progress.vid_id 
where conv_progress.vid_id is null
AND upload_progress.vid_id is null
ORDER BY video.timestamp DESC LIMIT ?, ?

I just want to count the total results for pagination usage. The current limit ?,? reduces my count if I use rowCount().

SELECT video.*, COUNT(vid_id) as count from video 
LEFT JOIN conv_progress on video.vid_id=conv_progress.vid_id 
LEFT JOIN upload_progress on video.vid_id=upload_progress.vid_id 
where conv_progress.vid_id is null
AND upload_progress.vid_id is null
ORDER BY video.timestamp DESC LIMIT ?, ?',$variables

This query only returns one result but a proper count number. Anyone know how I can fix this?

Upvotes: 0

Views: 123

Answers (2)

MPelletier
MPelletier

Reputation: 16697

I would simply calculate the total:

SELECT count(*) from video 
LEFT JOIN conv_progress on video.vid_id=conv_progress.vid_id 
LEFT JOIN upload_progress on video.vid_id=upload_progress.vid_id 
where conv_progress.vid_id is null
AND upload_progress.vid_id is null

Then, from the language you're using, determine the smallest of between the returned count and the number of rows your query would have limited with your two ? parameters. In PHP:

min($query_count,$upper_bound-$lower_bound)

Upvotes: 1

Marc B
Marc B

Reputation: 360762

MySQL has a sql_calc_found_rows extensions, which forces the DB engine to calculate how many rows WOULD have been retrieve if it wasn't for the limit statement. You can then retrieve this full-row-count with [SELECT found_rows()][1] in a separate query.

Basic syntax for the parent query would be:

SELECT sql_calc_found_rows video.*  FROM video
...
LIMIT ?,?

Upvotes: 7

Related Questions