How to implement sql query in yii2 search model?

Question

I have following query:

      SELECT hs.*FROM hire_screening hs 
      INNER JOIN (SELECT resume_id,MAX(created_date) AS MaxDateTime 
      FROM hire_screening GROUP BY resume_id) hire_screening 
      ON hs.resume_id = hire_screening.resume_id 
      AND hs.created_date = hire_screening.MaxDateTime

I tried this:

   $query = HireScreening::find()
           ->select(["hs.resume_id","MAX(hs.created_date) AS MaxDateTime"])
           ->innerJoin('hire_screening as hs','hs.resume_id = hire_screening.resume_id')

          ->where(['hire_screening.created_date' => 'MaxDateTime'])
          ->orderBy(['(hs.created_date)' => SORT_DESC])
          ->groupBy(['hs.resume_id']);

When I use group by the result shows the first values of each 'resume_id' in the order that stored in the table. I just need the most latest distinct resume_id 's according to created_date. How can I implement this query in yii2 search model? Please help.

Please update question with code u have tried at this point. See How to ask article to improve your question. Remember that Stack Overflow is not your personal online query converter. — Yupik
– Yupik, Commented May 25, 2017 at 5:49
Am sorry. Am a beginner in Yii2. So please excuse any mistake. Question is updated. — Dijo
– Dijo, Commented May 25, 2017 at 6:49

vaishali · Accepted Answer · 2017-05-25 07:47:15Z

1

If you want to execute the raw sql query means, then you can use createCommand function. But it will return the array so in grid you should use array data provider.

$array= Yii::$app->db->createCommand("
      SELECT hs.*FROM hire_screening hs 
      INNER JOIN (SELECT resume_id,MAX(created_date) AS MaxDateTime 
      FROM hire_screening GROUP BY resume_id) hire_screening 
      ON hs.resume_id = hire_screening.resume_id 
      AND hs.created_date = hire_screening.MaxDateTime")->queryAll();

answered May 25, 2017 at 7:47

vaishali

3252 silver badges11 bronze badges

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

Collectives™ on Stack Overflow

How to implement sql query in yii2 search model?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related