Calculate total events per city based on lots of HABTM conditions in CakePHP / MySQL

On my "Event Listings" page, I list all the events that are happening NOW or later.

The user can then select which city(s), event type(s), event sub type(s)...etc they want to query against to return only events that match their selected item(s).

I have this working just fine - I'm building my joins/conditions... etc based on their input, and it's returning the correct events w/ pagination..etc.

The problem now is - I have a list of cities on the left of the page, and want to have the number of events in those cities (same with type, sub type..etc) - ie:

New York (16)
Chicago (15)
Austin (8)
...

Sports (6)
  - Baseball (2)
  - Basketball (2)
  - Football (0)
Outdoors (5)
...

It wouldn't be a big deal except that I'm using joins to get the data I need, so when I try this:

SELECT COUNT(`VenueCity`.`id`) + COUNT(RestaurantCity.id) AS total,
`VenueCity`.`name`, `VenueCity`.`id`, `VenueCity`.`slug`, `RestaurantCity`.`name`,
`RestaurantCity`.`id`, `RestaurantCity`.`slug` FROM `events` AS `Event`
INNER JOIN schedules AS `Schedule` ON (`Schedule`.`event_id` = `Event`.`id`)
INNER JOIN dates AS `Date` ON (`Date`.`schedule_id` = `Schedule`.`id`)
LEFT JOIN restaurants AS `Restaurant` ON (`Restaurant`.`id` = `Event`.`restaurant_id`) LEFT JOIN venues AS `Venue` ON (`Venue`.`id` = `Event`.`venue_id`)
LEFT JOIN cities AS `VenueCity` ON (`Venue`.`city_id` = `VenueCity`.`id`)
LEFT JOIN cities AS `RestaurantCity` ON (`Restaurant`.`city_id` = `RestaurantCity`.`id`)
WHERE `Event`.`name` IS NOT NULL
AND `Event`.`approval_status_id` = 1
AND `Date`.`start` >= '2011-07-12 16:45:39'
GROUP BY `VenueCity`.`id`
ORDER BY `total` DESC 

It's returning the count for EVERY date that exists (ie - if there are 10 events, each having 5 dates, it would show 50 as the count).

Not to mention, I'm having to run this complicated query AGAIN, after already running it twice for Cake's pagination. And I assume if I keep down this path, I'll have to do it again for every type, sub type, and sub sub type as well.

TLDR:

I have a complicated, mult-join query that gets events based on Event-Schedule->Date. I need to also be able to tell (whether in the same query or a new one(s)) how many events are happening for each city, type, sub type...etc etc.

I'm crossing my fingers the an开发者_JS百科swer is "oh, that's easy, you can just do __" - but will take any advice/help you have.

Try a subquery on the schedules table, something like this would get you one random date (most likely the first date that was entered in the table) for each event. Might need a bit of debugging, but this should get you there...

SELECT COUNT(`VenueCity`.`id`) + COUNT(RestaurantCity.id) AS total,
`VenueCity`.`name`, `VenueCity`.`id`, `VenueCity`.`slug`, `RestaurantCity`.`name`,
`RestaurantCity`.`id`, `RestaurantCity`.`slug` FROM `events` AS `Event`
INNER JOIN 

(SELECT MIN(subSchedule.`id`) AS `id`,subSchedule.`event_id` 
   FROM `schedules` AS subSchedule
   INNER JOIN dates AS subDate ON (subDate.`schedule_id` = subSchedule.`id`)   
   WHERE `subDate`.`start` >= '2011-07-12 16:45:39'
   GROUP BY subSchedule.`event_id`
) AS Schedule

    ON (`Schedule`.`event_id` = `Event`.`id`)

INNER JOIN dates AS `Date` ON (`Date`.`schedule_id` = `Schedule`.`id`)
LEFT JOIN restaurants AS `Restaurant` ON (`Restaurant`.`id` = `Event`.`restaurant_id`) LEFT JOIN venues AS `Venue` ON (`Venue`.`id` = `Event`.`venue_id`)
LEFT JOIN cities AS `VenueCity` ON (`Venue`.`city_id` = `VenueCity`.`id`)
LEFT JOIN cities AS `RestaurantCity` ON (`Restaurant`.`city_id` = `RestaurantCity`.`id`)
WHERE `Event`.`name` IS NOT NULL
AND `Event`.`approval_status_id` = 1
AND `Date`.`start` >= '2011-07-12 16:45:39'
GROUP BY `VenueCity`.`id`
ORDER BY `total` DESC 

@Dave, I don't seem to be able to comment, I assume it's because I'm new to StackOverflow. But basically what you are trying to do with the subquery is build the a pseudo table that has one row for each event. The results of that subquery will be used in the outter query just like a real table. I believe you can get what you want by grouping on the event_id, and then using a summary function to get the other fields you want. MIN() will get you the lowest schedule.id, and with the INNER JOIN to the date and the where clause, it will be the lowest ID that matches your where clause. You can actually test the subquery separately to fine tune it.

Turns out, I just needed a bit of a tweak. Instead of:

COUNT(`VenueCity`.`id`) + COUNT(RestaurantCity.id) AS total

I used this:

COUNT(DISTINCT Event.id) AS total

So it's still grouping by the city, but the count is based on the event, not the city. Should have been an easy one to catch, but - live'n'learn! :)

Complete query:

SELECT COUNT(DISTINCT Event.id) AS total, `VenueCity`.`name`, `VenueCity`.`id`, 
`VenueCity`.`slug`, `RestaurantCity`.`name`, `RestaurantCity`.`id`, 
`RestaurantCity`.`slug` FROM `events` AS `Event` 
INNER JOIN schedules AS `Schedule` ON (`Schedule`.`event_id` = `Event`.`id`) 
INNER JOIN dates AS `Date` ON (`Date`.`schedule_id` = `Schedule`.`id`) 
LEFT JOIN restaurants AS `Restaurant` ON (`Restaurant`.`id` = `Event`.`restaurant_id`)  
LEFT JOIN venues AS `Venue` ON (`Venue`.`id` = `Event`.`venue_id`) 
LEFT JOIN cities AS `VenueCity` ON (`Venue`.`city_id` = `VenueCity`.`id`) 
LEFT JOIN cities AS `RestaurantCity` ON (`Restaurant`.`city_id` = `RestaurantCity`.`id`)  
WHERE `Event`.`name` IS NOT NULL AND `Event`.`approval_status_id` = 1 
AND `Date`.`start` >= '2011-07-12 17:59:24' 
GROUP BY `VenueCity`.`id` ORDER BY `total` DESC