SELECT query with WHERE clause in Yii2 find () / QueryBuilder

Question

I managed to find simple examples of constructing a subquery, but I could not find a solution or find a solution when I need to include the WHERE clause. I am trying to simulate the following statement ...

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

I think I need something like ...

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

But this gives me an error: Column not found: 1054 Unknown column "parentTable.id" in the "where" section

EDIT: Including the names of real classes / tables ...

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

Answer 1

Thanks to Used_By_Already and Mike Ross, your answers helped me come to the final final 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');

Answer 2

SQL " " select, .

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

, , , " " select " ", :

SELECT ParentTable.*,c.MxNum
FROM ParentTable
LEFT JOIN (
           SELECT ChildTable.FK_Id, MAX(ChildTable.NumberField) as MxNum FROM ChildTable
           GROUP BY ChildTable.FK_Id
           ) AS c ON c.FK_Id = ParentTable.Id

: select NULL, - LEFT OUTER JOIN ( LEFT JOIN), NULL, , NULL , INNER JOIN.

Yii2, , , , .

Answer 3

, , , event area_interest

 $query = new Query();
 $subquery = new Query();
 $subquery ->select(['area_intrest.interest'])
            ->from('area_intrest' )
            ->where('area_intrest.id = event.interest_id');

 $query ->select(['event.title',$query3])
            ->from('event');
 $command = $query->createCommand();
 $data = $command->queryAll();

max , .

Answer 4

$submodel = ChildClass::find()
          ->select([ 'ChildTable.fk_id', 'MAX(ChildTable.NumberField)', ])
          ->where("...")
          ->asArray();

$model    = ParentClass::find()
          ->select([ 'parentTable.*', 'b.*', ])
          ->innerJoin(['b' => $submodel], 'b.`fk_id` = parentTable.`id`')
          ->all();