3

Using the query below, I can search for properties within a given radius and results are returned.

SELECT id, address, ( 3959 * acos( cos( radians( 53.184815 ) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-3.025741) ) + sin( radians(53.184815) ) * sin( radians( lat ) ) ) ) AS distance
FROM properties
WHERE area = 1 HAVING distance <= 1
ORDER BY price DESC, distance ASC
LIMIT 0, 10

However I now want to add pagination, thus the "LIMIT 0, 10" but somehow have the query return the total results. For example, if there are 100 results but we're only limiting to the first 10 results, return the total as 100.

I tried adding "COUNT(*) AS total" after the select but this caused zero results to be returned.

How do I have the query return the total in this way?

Reado
  • 1,412
  • 5
  • 21
  • 51
  • SQL_CALC_FOUND_ROWS and after SELECT FOUND_ROWS(); – splash58 Jul 20 '16 at 09:36
  • SQL_CALC_FOUND_ROWS is apparently up to 10 times slower: http://stackoverflow.com/questions/186588/which-is-fastest-select-sql-calc-found-rows-from-table-or-select-count – Reado Jul 20 '16 at 09:38
  • total means, the number of rows or the total property as number? – Avishake Jul 20 '16 at 09:39
  • I need to know the total number of properties that match the WHERE and HAVING statement. For example, if there are 100 results but we're only limiting to the first 10 results, return the total as 100. – Reado Jul 20 '16 at 09:40
  • 2
    @Reado From there = *If you have an INDEX on your where clause (if id is indexed in your case), then it is better not to use SQL_CALC_FOUND_ROWS and use 2 queries instead, but if you don't have an index on what you put in your where clause (id in your case) then using SQL_CALC_FOUND_ROWS is more efficient.* - It's your case – splash58 Jul 20 '16 at 09:41

2 Answers2

2

I think it will need a subquery to achieve that:

SELECT
    id, address, ( 3959 * acos( cos( radians( 53.184815 ) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-3.025741) ) + sin( radians(53.184815) ) * sin( radians( lat ) ) ) ) AS distance,
    (SELECT count(*) FROM properties WHERE area = 1 HAVING ( 3959 * acos( cos( radians( 53.184815 ) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-3.025741) ) + sin( radians(53.184815) ) * sin( radians( lat ) ) ) )<= 1) AS total
FROM properties
WHERE area = 1 HAVING distance <= 1
ORDER BY price DESC, distance ASC
LIMIT 0, 10
Blank
  • 12,308
  • 1
  • 14
  • 32
1

You either have to use a separate query without limit with count(*) or as splash indicated, use SQL_CALC_FOUND_ROWS in your query and then issue a SELECT FOUND_ROWS(); to get the total number.

You can try to inject the count(*) query as a subquery in your main query, but to me that's only unnecessary complication of your query.

Shadow
  • 33,525
  • 10
  • 51
  • 64