CODEWITHSUNDEEP
mysqlyii2
J.H
J.H

Reputation: 37

mysql select subquery in yii2

Hi i'd like to let this

ID | Forename | Surname | Created
---------------------------------
 1 | Tom      | Smith   | 2008-01-01
 1 | Tom      | Windsor | 2008-02-01
 2 | Anne     | Thorn   | 2008-01-05
 2 | Anne     | Baker   | 2008-03-01
 3 | Bill     | Sykes   | 2008-01-20

becomes like this

ID | Forename | Surname | Created
---------------------------------
 1 | Tom      | Windsor | 2008-02-01
 2 | Anne     | Baker   | 2008-03-01
 3 | Bill     | Sykes   | 2008-01-20

so I make a mysql query : 

SELECT 
  * 
FROM tableA as A 
WHERE created = (
    SELECT 
        MAX(created) 
    FROM tableA GROUP BY id having id=A.id
);

and it works in mysql as well , I can get what i want.

but i dont know how to write this in yii2 active

I have tried :

 $query = (new \yii\db\Query())
                        ->select(['A.*'])
                        ->from('tableA AS A')
                        ->where('created = (SELECT MAX(created) from tableA GROUP BY id having id=A.id');

$command = $query->createCommand();
$data = $command->queryAll();

but it didn't work.

thanks.

Upvotes: 0

Views: 848

Answers (2)

oakymax
oakymax

Reputation: 1474

Try this

$subQuery = (new \yii\db\Query())
  ->select(['id', 'max_created' => 'MAX(created)'])
  ->from('tableA')
  ->groupBy('id'); 

$query = (new \yii\db\Query())
  ->select(['A.*'])
  ->from('tableA AS A')
  ->leftJoin(['B' => $subQuery], 'B.id = A.id')
  ->where('A.created = B.max_created');

$command = $query->createCommand();
$data = $command->queryAll();

You can get details here: Yii2 guide section for Query->leftJoin

And i found related topic here: SELECT sub-query with WHERE condition in Yii2 find() / QueryBuilder

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

Incidentally, assuming a PK on (id,created), a better (i.e. more efficient) way to write that query would be:

SELECT x.* 
  FROM my_table x 
  JOIN 
     ( SELECT id,MAX(created) created FROM my_table GROUP BY id ) y 
    ON y.created = x.created 
   AND y.id = x.id;

Upvotes: 0

Related Questions