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

Question

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

I know that my class names and table names are correct. Nevertheless, edited my question above accordingly. — Shahid Thaika
– Shahid Thaika, Commented Oct 18, 2015 at 18:30
Could you also include the sql statement as printed out in the error? — topher
– topher, Commented Oct 18, 2015 at 18:33
You are trying to execute (by using max()) the sub query before the main one... — soju
– soju, Commented Oct 18, 2015 at 19:41

Shahid Thaika · Accepted Answer · 2015-10-29 05:28:03Z

3

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

answered Oct 29, 2015 at 5:28

Shahid Thaika

2,3457 gold badges33 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paul Maxwell · Accepted Answer · 2015-10-19 02:47:43Z

The example query, from a SQL perspective is using a "correlated subquery" inside the select clause and often this is a very inefficient way of forming a query.

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

Although it may appear at first glance to be more complex and hence less efficient, it is generally better for performance to avoid "correlated subqueries" in a select clause and substitute using a "derived table" instead, like this:

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

Note, a correlated subquery with a select clause might return NULL, and due to this, if replacing them with a derived table the equivalent join type is a LEFT OUTER JOIN (or simply LEFT JOIN) as this also permits a NULL result. However if you don't need NULLs for the column, then use the more efficient INNER JOIN instead.

Apologies in advance for not knowing Yii2 syntax, but it seems relevant to know an effective alternative approach which may assist in solving the issue.

Mike Ross · Accepted Answer · 2015-10-18 22:44:14Z

1

Try this because it works fine for me, i had similar situation where my table names are event and 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();

In your condition you can even use max in the subquery in quotes and it will work fine.

answered Oct 18, 2015 at 22:44

Mike Ross

2,9725 gold badges54 silver badges107 bronze badges

Comments

wscourge · Accepted Answer · 2016-12-09 11:46:38Z

0

Try this one

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

edited Dec 9, 2016 at 11:46

wscourge

11.4k17 gold badges65 silver badges87 bronze badges

answered Dec 9, 2016 at 11:02

Senthilkumar T

11 bronze badge

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related