Как сделать min() по join таблице в построителе запросов?

Question

[regex29]

Как сделать min() по join таблице в построителе запросов yii2?
Пробую такой вариант, но в employee возвращает пустой массив.

return Department::find()->joinWith(['employee'])
    ->select('MIN(employee.salary) AS minSalary, name, department.image')
    ->groupBy('department.id')
    ->all();

или как правильно преобразовать такой sql запрос в activerecord?

SELECT department.*, MIN(employee.salary ) AS minSalary 
    FROM `department` 
    INNER JOIN employee ON department.id = employee.employeeId
    GROUP BY department.id;

Comments

[Akina]

как правильно преобразовать такой sql запрос в activerecord?

SELECT department.*, MIN(employee.price) AS minSalary 
    FROM `department` 
    INNER JOIN employeec ON department.id = employee.employeeId
    GROUP BY department.id;

Сам запрос синтаксически ошибочен. Т.н. incomplete grouping.

Answer 1

[Мирон]

SQL
---

select department.*
         , coalesce(dptSlr.minSalary, 0.00) as minSalary
FROM `department` 
left outer join 
(SELECT employee.departmentId, MIN(employee.salary ) AS minSalary 
    FROM employee
    GROUP BY employee.departmentId) dptSlr
on department.id = dptSlr.departmentId;