Shahid Thaika Shahid Thaika - 1 year ago 849
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()

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

Including actual class/table names...

$endQuery = UnitSchedule::find()
$query = CourseSchedule::find();

Answer Source

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 =');