Cakephp Showing count of items by relationship

Question

I'm using CakePHP 3.9 and i have the following code

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

But is returning me the following:

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

What i want is count the amount of registries based on the relationship contacts_categories

I can make a query but is more clean and efficient doing with 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();

Not tested, but something along these lines should help you find the right solution. It basically finds all related Contacts to ContactsCategories (If you set up foreign keys and data structures as the cake convention says to do so), groups them by the contacts_category_id and then counts them