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

Created: 8 Oct '16

Last Reply: 11 Oct '16

Replies: 1

Views: 1809

Votes: 0

Welcome to Aero Coding!

Aero Coding is a CakePHP-focused Q&A community for professional and enthusiast cake bakers. It's built and run by you as part of the community.


Join Now Tour

Download Cakephp

Start baking your own CakePHP application!


Cakephp All Versions