How to get total count of this query without calling another one?

Question

I have a query that I need to get a total count of before I paginate. I was wondering if there was a way to return the total count within the same query before the limit is executed. I have to pass the total value to my back-end if thats possible.

What the query does-
-Finds rows that have are within a specified radius,
-that match a user entered keyword,
-orders by date then limit,
-offset for pagination.

SELECT *,   
( 3959 * acos (cos ( radians(?) )* cos( radians( lat ) )*cos( radians( lng ) - radians(?) )+ sin ( radians(?) )* sin( radians( lat ) ))) AS distance   
  FROM job_posting  
 where job_title like ?  
 HAVING distance < ?   
ORDER BY date_created DESC   
limit ?, 25

I've looked at a couple different examples, but not sure how to implement it on this query. Any advice helps.

remember a query return a result pictured as a table, how do you want to materialize your answer ? (make 3 fakes return lines as you want them, we'll see if there is a way in SQL to do it) ; But as far as I see, you'll probably have to do two query... — Blag, Dec 07 '16 at 01:06

score 1 · Answer 1 · answered Dec 07 '16 at 02:02

Not efficiently, and not particularly gracefully. Counting all items that match criteria is a different enough operation from retrieving a certain chunk of rows that there's little to be gained by combining them.

Logically, any single statement that returned both the rows and the total rowcount would in fact comprise two queries clumsily mashed together, and the count would be appended to each and every row when you only need it once. You could do it by JOINing in the SELECT COUNT... as a subquery ON TRUE, but you're likely better off factoring out pagination logic in your backend to run the count and retrieval queries, especially if it's a common need in your application.

score 0 · Answer 2 · edited May 23 '17 at 12:08

Technically it is possible to this using sql_calc_found_rows modifier in the select statement and then retrieving the count with found_rows() function.

SELECT sql_calc_found_rows *,   
( 3959 * acos (cos ( radians(?) )* cos( radians( lat ) )*cos( radians( lng ) - radians(?) )+ sin ( radians(?) )* sin( radians( lat ) ))) AS distance   
  FROM job_posting  
 where job_title like ?  
 HAVING distance < ?   
ORDER BY date_created DESC   
limit ?, 25;

select found_rows();

However, as this excellent SO topic discusses, most of the time it is faster to execute another select count(*)... query.

Do test both ways and use whichever is faster for you.

How to get total count of this query without calling another one?

2 Answers2