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

我想要的是根据 contact_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 相关的所有联系人（如果您按照蛋糕约定设置外键和数据结构），按 contact_category_id 对它们进行分组，然后对它们进行计数