mysql select subquery in yii2

Question

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.

It is not very clear what you are trying to accomplish. Try to define it in words first and then will build the query for it. And this does not seem like an YII2 issue, just a regular mySql query. — Elzo Valugi
– Elzo Valugi, Commented Jul 13, 2016 at 10:34
@Strawberry I think thats not the problem . cuz i can work with mysql query. I got the other serial as my primary key — J.H
– J.H, Commented Jul 13, 2016 at 10:53

Community · Accepted Answer · 2017-05-23 11:51:26Z

1

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

edited May 23, 2017 at 11:51

CommunityBot

11 silver badge

answered Jul 13, 2016 at 20:15

oakymax

1,4741 gold badge14 silver badges21 bronze badges

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

Comments

Strawberry · Accepted Answer · 2016-07-13 11:11:04Z

0

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;

answered Jul 13, 2016 at 11:11

Strawberry

34k14 gold badges43 silver badges58 bronze badges

Collectives™ on Stack Overflow

mysql select subquery in yii2

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related