[RESOLVED] how to get COUNT for this query

sneakyimp

My page uses this query:

SELECT e.id, e.title, e.subheading, eta.start_timestamp, eta.end_timestamp, e.zip, e.bold, e.outline, e.color, e.subheading, COUNT(esa.id) AS subcat_count

FROM demo_event_time_assoc eta,
  demo_events e,
  demo_event_subcategory_assoc esa,
  demo_zip_codes z

WHERE eta.event_id=e.id
  AND esa.event_id=e.id
  AND z.zip=e.zip
  AND e.active=1 
  AND esa.subcategory_id IN (1,2,3,4,5,6,7,8,9,10)
  AND (
	(eta.start_timestamp >= 1162281600 AND eta.start_timestamp <= 1162368000)
	OR (eta.end_timestamp < 1162281600 AND eta.end_timestamp > 1162285200))

GROUP BY eta.id ORDER BY subcat_count DESC, eta.id

As I try to implement a prev/next paging links for this page, i'm putting in a LIMIT query but I want a query to determine the total number of records. I tried this:

SELECT COUNT(eta.id) as total_records
FROM demo_event_time_assoc eta, demo_events e, demo_event_subcategory_assoc esa, demo_zip_codes z
WHERE eta.event_id = e.id
AND esa.event_id = e.id
AND z.zip = e.zip
AND e.active =1
AND esa.subcategory_id
IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 ) 
AND (
(
eta.start_timestamp >=1162281600
AND eta.start_timestamp <=1162368000
)
OR (
eta.end_timestamp <1162281600
AND eta.end_timestamp >1162285200
)
)
GROUP BY eta.id DESC , eta.id

But it returned a recordset with twelve records (the total number of records returned from the original query) with each total_records set to 1.

Piranha

You need to use more values to group by, I would test to use the primary key from each of the included tables.

sneakyimp

Piranha wrote:
You need to use more values to group by, I would test to use the primary key from each of the included tables.

EDIT: Let's see if I can make this clearer

I have tried grouping by all the primary keys in each table as well as all fields mentioned in the query like this:

 SELECT COUNT(*) as total_records
FROM demo_event_time_assoc eta, demo_events e, demo_event_subcategory_assoc esa, demo_zip_codes z
WHERE eta.event_id = e.id
AND esa.event_id = e.id
AND z.zip = e.zip
AND e.active =1
AND esa.subcategory_id
IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 )
AND (
(
eta.start_timestamp >=1162281600
AND eta.start_timestamp <=1162368000
)
OR (
eta.end_timestamp <1162281600
AND eta.end_timestamp >1162285200
)
)
GROUP BY eta.id, esa.event_id, esa.subcategory_id, e.id, e.active

Both approaches still return multiple records rather than a single count value. In most cases, adding more keys in the GROUP BY clause also changes the number of records returned. Rather than just the records returned in the original query (where separate esa associations are combined into a single count value for each record), we a separate record contributed by each time association record (eta) AND each subcategory association record (esa).

I don't really understand what is going on here. I was under the impression (as piranha seems to have suggested) that adding additional GROUP BY fields would further reduce the number of records shown. I think that somehow things are getting confused because I have two association tables here - one for time and one for subcategories. If there are multiple time associations for a given event, I want distinct records. If there are multiple subcategory associations for a given event, I don't. Does that make sense?

In other words, if Joe's BBQ occurs on Tuesday and Wednesday within the search time frame, I want Joes' BBQ to occur twice. On the other hand, if Joe's BBQ has five different subcats associated with it, it will still only appear twice.

On the other hand, we still only want to show events that are associated with the current subcategories being searched. In this case, those with IDs IN (1,2,3,4,5,6,7,8,9,10).

sneakyimp

Ok after tireless research and consulting a mentally unstable but masterful sql specialist friend of mine, I have two approaches.

APPROACH 1
the unstable guy said do this. it's simple and seems to work:


SELECT COUNT(DISTINCT eta.id)
FROM demo_event_time_assoc eta,
demo_events e,
demo_event_subcategory_assoc esa,
demo_zip_codes z
WHERE eta.event_id=e.id
AND esa.event_id=e.id
AND z.zip=e.zip
AND e.active=1
AND esa.subcategory_id IN (1,2,3,4,5,6,7,8,9,10)
AND (
(eta.start_timestamp >= 1162281600 AND eta.start_timestamp <= 1162368000)
OR
(eta.end_timestamp < 1162281600 AND eta.end_timestamp > 1162285200))
AND (3963.20477315*(2*asin(sqrt(POWER(sin((0.595334589 56 -z.lat_radians)/2),2) + cos(0.59533458956)*cos(z.lat_radians)*POWER(sin((-2.06592416764 - z.long_radians)/2),2)))) < 50)

So I can live with that -- unless anybody sees how it might bring back the wrong result. It looks pretty good to me though.

APPROACH 2
This is only available in MySQL 4.0.0 or higher but looks pretty rad. I'm wondering if it's reliable? faster? a bad idea? It seems too good to be true. Is there any way to determine your MySQL version to know if this would be ok?

Run the original query but add 'SQL_CALC_FOUND_ROWS' before the selected fields. Go ahead and include your LIMIT clause because you are paging.

SELECT SQL_CALC_FOUND_ROWS e.id, e.title, e.subheading, eta.start_timestamp, eta.end_timestamp, e.zip, e.bold, e.outline, e.color, e.subheading, COUNT(esa.id) AS subcat_count
FROM demo_event_time_assoc eta,
demo_events e,
demo_event_subcategory_assoc esa,
demo_zip_codes z
WHERE eta.event_id=e.id
AND esa.event_id=e.id
AND z.zip=e.zip
AND e.active=1
AND esa.subcategory_id IN (1,2,3,4,5,6,7,8,9,10)
AND (
(eta.start_timestamp >= 1162281600 AND eta.start_timestamp <= 1162368000)
OR
(eta.end_timestamp < 1162281600 AND eta.end_timestamp > 1162285200))
AND (3963.20477315*(2*asin(sqrt(POWER(sin((0.59533458956 -z.lat_radians)/2),2) + cos(0.59533458956)*cos(z.lat_radians)*POWER(sin((-2.06592416764 - z.long_radians)/2),2)))) < 50)
GROUP BY eta.id
ORDER BY subcat_count DESC, eta.id
LIMIT 0, 50

Then run this query...it will return the total number of rows that met your criteria, ignoring the LIMIT clause:

SELECT FOUND_ROWS() AS total_records