CODEWITHSUNDEEP
mysqlyii2yii2-advanced-app
Dijo
Dijo

Reputation: 73

How to use MAX function in yii2 query?

I have a 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

This is my table:

enter image description here

I need the answer like this:

enter image description here

I tried this:

    $query = HireScreening::find()-
             ->select(["hire_screening.screening_id","hs.resume_id",
            "MAX(hs.created_date) AS 
             MaxDateTime","hs.screening_by","hsl.screening_level as 
             hr_level","hss.screening_status as 
             hr_status","hr.candidate_name","hsm.screening_mode as 
          hr_mode","hire_screening.created_date","hs.screening_date"])
            ->innerJoin('hire_screening as hs','hs.resume_id = 
            hire_screening.resume_id')
            ->leftJoin('hire_screening_level as 
           hsl','hire_screening.screening_level = hsl.id')
            ->leftJoin('hire_screening_mode as 
           hsm','hire_screening.screening_mode = hsm.id')
            ->leftJoin('hire_screening_status as 
           hss','hire_screening.screening_status = hss.id')
            ->leftJoin('hire_resume as 
           hr','hire_screening.resume_id=hr.resume_id')
            //->where(['hire_screening.created_date = MaxDateTime'])
            ->groupBy(['resume_id']);
            //->having(['hire_screening.created_date' => 
          'hs.MaxDateTime']);
           $query->orderBy(['created_date' => SORT_DESC]);

But it didn't shows the answer. I need the distinct resume_id's with latest created date. The sql query shows the correct answer.I want to write this query in my search model. Please help me to convert this query into yii2.

Upvotes: 0

Views: 1245

Answers (1)

Alexander
Alexander

Reputation: 4527

If there is a table MyTable, then you can use simplier query grouping the table by resume_id:

SELECT 
  screening_id, 
  resume_id, 
  screening_by, 
  screening_date, 
  screening_level, 
  screening_mode, 
  screening_status, 
  reject_reason, 
  remarks, 
  created_by, 
  MAX(created_date) AS created_date
FROM MyTable
GROUP BY resume_id;

Using simplify query helps to avoid an errors. Also you could create a view or a stored procedure using the query and call this from your PHP code.

Upvotes: 1

Related Questions