CODEWITHSUNDEEP
sqlcakephpcakephp-3.0
pepito
pepito

Reputation: 513

Add where clause to calculated field in cakephp 3 query

I have a query in which I want the name of a company and its employee quantity. The thing is I want to filter this result by some conditions (like employee_number > 50 etc.). My problem is that, when building the query, I don't know how to filter this result, as the condition is set over a calculated field, so when applying the condition it gives me the below

Error: `SQLSTATE[42S22]: Column not found: 1054 Unknown column 'employee_number' in 'where clause'`.

I have been trying different things, but this is what I currently have:

$query = $this->Companies->find('all')->where($conditions)->contain(['Users']);
        

$query
   ->select(['Users.name',
   'Company.modified',
   'employee_number' => $query->func()->count('DISTINCT(Employees.id)')])
   ->where('employee_number >' => 50 )
   ->leftJoinWith('Employees', function (\Cake\ORM\Query $query) {
       return $query->where(['deleted' => 0]);
   })
   ->group(['Employees.company_id', 'Company.id']);

Upvotes: 0

Views: 464

Answers (1)

ndm
ndm

Reputation: 60463

First things first, you cannot refer to an aggregate in the WHERE clause, as grouping happens afterwards, hence the error, the field employee_number doesn't exist when the WHERE conditions are being applied, you have to leverage the HAVING clause instead.

Depending on the DBMS that you are using you can reference the column from the select list, MySQL for example allows that:

$query
    ->select([
        'Users.name',
        'Company.modified',
        'employee_number' => $query->func()->count('DISTINCT Employees.id')
    ])
    ->leftJoinWith('Employees', function (\Cake\ORM\Query $query) {
        return $query->where(['deleted' => 0]);
    })
    ->group(['Employees.company_id', 'Company.id'])
    ->having(['employee_number >' => 50]);

while Postgres for example doesn't, and requires you to repeat the aggregation inside of the HAVING clause:

->having(function (
    \Cake\Database\Expression\QueryExpression $exp,
    \Cake\ORM\Query $query
) {
    return $exp->gt($query->func()->count('DISTINCT Employees.id'), 50);
});

ps. using DISTINCT should only be necessary when you have for example multiple joins that would result in duplicate joined rows.

See also

Upvotes: 2

Related Questions