Yii2中具有WHERE条件的SELECT子查询find()/QueryBuilder

问题描述

我能够找到构建子查询的简单示例，但是当我需要包含WHERE条件时，我无法找出或找到解决方案.我正在尝试模拟以下语句...

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

但是它给我一个错误:找不到列:1054"where子句"中的未知列"parentTable.id"

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

包括实际的类/表名...

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

推荐答案

感谢Used_By_Already和Mike Ross，您的回复帮助我达成了以下最终的完整Yii2/MySQL解决方案.

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

这篇关于Yii2中具有WHERE条件的SELECT子查询find()/QueryBuilder的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！