Category: Models & ORM

Q: In CakePHP 3, Inner join and count records

I want to count female employees per department in CRM, how to build custom query in CakePHP?

SELECT 
    EMP.DeptNo, DEP.DeptName, count(*) AS records
FROM 
    Employees AS EMP INNER JOIN Departments AS DEP
ON 
    EMP.DeptNo = DEP.DeptNo 
WHERE 
    EMP.Gender = 'F' 
GROUP BY 
    EMP.DeptNo, DEP.DeptName;

Need advice, thanks.

sql
1 Answers

Version: 3.3

You have to set up CakePHP Associations first.

Assume one department has many employees that is one to many, so you can set up a hasMany association in CakePHP.

class DepartmentsTable extends Table
{

    public function initialize(array $config)
    {
        //set up one to many association
        $this->hasMany('Employees', [
            'className' => 'Employees',
            'foreignKey' => 'DeptNo'
        ]);
    }
}

Then set up containable containable queries.

$query = $departments->find()
  ->contain(['Employees']) // EMP.DeptNo = DEP.DeptNo 
  ->select(['Employees.DeptNo', 'Departments.DeptName', 'records' => 'count(*)']) //count(*) AS records
  ->where(['Employees.Gender' => 'F'])
  ->group(['Employees.DeptNo', 'Departments.DeptName']);

$query->toArray();
Kristi
Kristi

137