Getting count fields of contained models using WHERE conditions

Question

I have three models, Companies, events and assistances, where the assistances table stores the event_id and the company_id. I'd like to get a query in which the total assistances of the company to certain kind of events are stored. Nevertheless, as all these counts are linked to the same table, I don't really know how to build this query effectively. I have the ids of the assistances to each kind of event stored in some arrays, and then I do the following:

$query = $this->Companies->find('all')->where($conditions)->order(['name' => 'ASC']);

$query
    ->select(['total_assistances' => $query->func()->count('DISTINCT(Assistances.id)')])
    ->leftJoinWith('Assistances')
    ->group(['Companies.id'])
    ->autoFields(true);

Nevertheless, I don't know how to get the rest of the Assistance count, as I would need to count not all the distinct assistance Ids but only those taht fit to certain conditions, something like ->select(['assistances_conferences' => $query->func()->count('DISTINCT(Assistances.id)')])->where($conferencesConditions) (but obviously the previous line does not work. Is there any way of counting different kind of assistances in the query itself? (I need to do it this way because I then plan to use pagination and sort the table taking those fields into consideration).

score 0 · Answer 1 · edited Dec 20 '20 at 07:58

The *JoinWith() methods accept a second argument, a callback that receives a query builder used for affecting the select list, as well as the conditions for the join.

->leftJoinWith('Assistances', function (\Cake\ORM\Query $query) {
    return $query->where([
        'Assistances.event_id IN' => [1, 2]
    ]);
})

This would generate a join statement like this, which would only include (and therefore count) the Assistances with an event_id of 1 or 2:

LEFT JOIN
    assistances Assistances ON
        Assistances.company_id = Companies.id AND
        Assistances.event_id IN (1, 2)

The query builder passed to the callback really only supports selecting fields and adding conditions, more complex statements would need to be defined on the main query, or you'd possibly have to switch to using subqueries.

Getting count fields of contained models using WHERE conditions

1 Answers1