Cakephp按关系显示项目数

Question

我使用的是CakePHP 3.9，我有以下代码

$contactsByGroup = $this->Contacts->find('all', [
'fields' => [
'ContactsCategories.id',
'ContactsCategories.name',
'COUNT(Contacts.id)',
],
'contain' => [
'ContactsCategories'
],
'group' => [
'Contacts.contacts_category_id'
]
])->autoFields(true);

但正在返回我以下内容：

SELECT
ContactsCategories.id AS `ContactsCategories__id`,
ContactsCategories.name AS `ContactsCategories__name`,
COUNT(Contacts.id) as `count` AS `COUNT(Contacts__id) as `count``,
Contacts.id AS `Contacts__id`,
Contacts.first_name AS `Contacts__first_name`,
Contacts.last_name AS `Contacts__last_name`,
Contacts.social_reason AS `Contacts__social_reason`,
Contacts.document_id AS `Contacts__document_id`,
Contacts.phone AS `Contacts__phone`,
Contacts.address AS `Contacts__address`,
Contacts.location AS `Contacts__location`,
Contacts.created AS `Contacts__created`,
Contacts.modified AS `Contacts__modified`,
Contacts.user_id AS `Contacts__user_id`,
Contacts.contacts_category_id AS `Contacts__contacts_category_id` 
FROM
contacts Contacts 
LEFT JOIN
contacts_categories ContactsCategories 
ON ContactsCategories.id = (Contacts.contacts_category_id)
GROUP BY
Contacts.contacts_category_id

我想要的是根据关系contacts_categories 计算注册数量

我可以进行查询，但使用find更干净、更高效。

SELECT
count(*),
contacts_categories.id,
contacts_categories.name 
FROM
contacts 
LEFT JOIN
contacts_categories 
ON contacts_categories.id = contacts.contacts_category_id 
GROUP by
contacts.contacts_category_id

Answer 1

$query = $this->Contacts->find('all')->contain(['ContactsCategories'])->group('contacts_category_id');
$registries= $query->count();

没有经过测试，但沿着这些思路应该可以帮助您找到正确的解决方案。它基本上会找到所有与ContactsCategories相关的Contacts(如果你按照蛋糕约定设置外键和数据结构(，按照Contacts_category_id对它们进行分组，然后对它们进行计数