当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - Laravel orderby with groupby sql

问题描述:

Hi I am trying the query with orderby and groupby together and came up with this solution which is working fine here is my query

SELECT p1.year_id,p1.tbl_wsp_master_id ,tbl_wsp_masters.WSP_Name

FROM tbl_wsp_years p1

INNER JOIN

(

SELECT max(year_id) MaxPostDate,tbl_wsp_master_id

FROM tbl_wsp_years

GROUP BY tbl_wsp_master_id

) p2

ON p1.tbl_wsp_master_id = p2.tbl_wsp_master_id

AND p1.year_id = p2.MaxPostDate

INNER JOIN tbl_wsp_masters

ON tbl_wsp_masters.id = p1.tbl_wsp_master_id

order by p1.year_id desc

but how do i do this in Laravel Eloquent.

Thank you for your any suggestions

网友答案:

I solved it this way thanks

DB::table('tbl_wsp_years as y1')
        ->join('tbl_wsp_masters', 'tbl_wsp_masters.id', '=', 'y1.tbl_wsp_master_id')
        ->select('tbl_wsp_masters.WSP_Name AS WSP_Name', 'y2.*')
        ->join(DB::raw('(SELECT MAX(year_id) MaxYear, tbl_wsp_master_id FROM tbl_wsp_years GROUP BY tbl_wsp_master_id) y2'), 
                function($join)
                {
                    $join->on('y1.tbl_wsp_master_id', '=', 'y2.tbl_wsp_master_id');
                    $join->on('y1.year_id','=','y2.MaxYear');
                })
        ->orderBy('y1.year_id', 'DESC')
        ->get();
分享给朋友:
您可能感兴趣的文章:
随机阅读: