There are two entities: employee and department. Communication - many to many. I have three tables: employee (id, name, salary), department (id, name) and department_employee (department_id, employee_id).

php code:

class Employee extends ActiveRecord { ... public function getDepartments() { return $this ->hasMany(Department::className(), ['id' => 'department_id']) ->viaTable('departments_employees', ['employee_id' => 'id']); } } class Department extends ActiveRecord { ... public function getEmployees() { return $this ->hasMany(Employee::className(), ['id' => 'employee_id']) ->viaTable('departments_employees', ['department_id' => 'id']); } }

Task: get the collection from the Department with all its values ​​+ maximum salary values ​​from the joined table. PS salary is integer.

UPD: Need to implement such a request

 "SELECT department.name, MAX(employee.salary), COUNT(employee.id) FROM `department` INNER JOIN departments_employees ON department.id = departments_employees.department_id INNER JOIN employee ON employee.id = departments_employees.employee_id GROUP BY department.id;"
  • one
    Looks like a learning task, show your aspirations to solve the problem, what exactly is the problem of implementation? -
  • Data from DB tables for table rendering (layout). The table contains columns: Department name, number of employees and maximum salary of an employee. I receive from DB thus Department :: find (). Those. the collection element has in its properties a department name and an Employment collection. How to correctly withdraw from the database the maximum employee salary for the department? - Alexander Bondarenko

1 answer 1

Documentation helped. Here is the formation of the query:

 Department::find() ->select(['MAX(employee.salary) AS maxSalary', 'COUNT(employee.id) AS quantity','department.id','department.name']) ->innerJoin('departments_employees', 'departments_employees.department_id = department.id') ->innerJoin('employee','departments_employees.employee_id = employee.id') ->groupBy('department.id') ->asArray()