Shahid Thaika Shahid Thaika - 6 months ago 463
SQL Question

SELECT sub-query with WHERE condition in Yii2 find() / QueryBuilder

I was able to find simple examples of sub-query building, but am unable to figure out nor find the solution when I need to include a WHERE condition. I am trying to simulate the following statement...

SELECT ParentTable.*, (SELECT MAX(ChildTable.NumberField)
FROM ChildTable
WHERE ChildTable.FK_Id = ParentTable.Id)
FROM ParentTable


Guess I'd need something like...

$query = ParentClass::find()
->addSelect(
ChildClass::find()
->where('childTable.fk_id=parentTable.id')
->max('childTable.field1')
);


But it gives me an error: Column not found: 1054 Unknown column 'parentTable.id' in 'where clause'

EDIT:
Including actual class/table names...

$endQuery = UnitSchedule::find()
->where('cm_courseschedule.id=cm_unitschedule.csch_id')
->max('cm_unitschedule.slot');
$query = CourseSchedule::find();
$query->addSelect($endQuery);

Answer

Thanks Used_By_Already and Mike Ross, your replies helped me arrive at the final complete Yii2/MySQL solution below.

$query = ParentClass::find();
$subQuery = ChildClass::find()->select('fk_id, max(column1) as column1')->groupBy('fk_id');
$query->leftJoin(['T' => $subQuery], 'T.fk_id = parentTable.id');