根据 CakePHP / MySQL 中的大量 HABTM 条件计算每个城市的总事件数

Question

在我的“事件列表”页面上，我列出了现在或以后发生的所有事件。

然后，用户可以选择他们想要查询的城市、事件类型、事件子类型等，以仅返回与其所选项目匹配的事件。

我的这个工作得很好 - 我正在根据他们的输入构建我的连接/条件...等，并且它返回带有分页的正确事件...等。

现在的问题是 - 我在页面左侧有一个城市列表，并且想要这些城市中的事件数量（与类型、子类型等相同） - 即：

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

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

这不会是一个大的交易除了我使用联接来获取我需要的数据外，所以当我尝试这样做时：

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 

它返回每个存在的日期的计数（即 - 如果有 10 个事件，每个事件有 5 个日期，它将显示 50 作为数数）。

更不用说，在为 Cake 的分页运行两次之后，我必须再次运行这个复杂的查询。我假设如果我继续走这条路，我也必须对每种类型、子类型和子子类型再次执行此操作。

TLDR：

我有一个复杂的多连接查询，它根据事件计划->日期获取事件。我还需要能够告诉（无论是在同一个查询中还是在新的查询中）每个城市、类型、子类型等发生了多少事件。

我祈祷答案是“哦，这很简单，你可以做 __” - 但会接受你的任何建议/帮助。

Answer 1

尝试在时间表表上进行子查询，类似这样的操作将为每个事件提供一个随机日期（很可能是在表中输入的第一个日期）。可能需要一些调试，但这应该可以帮助您...

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，我似乎无法发表评论，我认为这是因为我是 StackOverflow 的新手。但基本上，您尝试使用子查询执行的操作是构建一个伪表，其中每个事件都有一行。该子查询的结果将像真实表一样在外部查询中使用。我相信您可以通过对 event_id 进行分组，然后使用汇总函数来获取您想要的其他字段来获得您想要的内容。 MIN() 将为您提供最低的 Schedule.id，并且通过 INNER JOIN 到日期和 where 子句，它将是与您的 where 子句匹配的最低 ID。您实际上可以单独测试子查询以对其进行微调。

Answer 2

事实证明，我只需要进行一些调整。而不是：

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

我使用了这个：

COUNT(DISTINCT Event.id) AS total

所以它仍然按城市分组，但计数是基于事件，而不是城市。应该很容易抓住，但是 - 活到老，学到老！ :)

完整查询：

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