dongweihuan8832 2017-10-30 09:37
浏览 104
已采纳

Laravel Sub Query Group of Group By

I need help in getting the max claim_id of a table that is grouped by claim_company_id in laravel.

Here is the table sample

claim_id         claim_company_id       Date
1                     1                  1/1/2010
2                     1                  1/2/2010
3                     7                  1/3/2010
4                     7                  1/4/2010
5                     7                  1/5/2010

SELECT * FROM `claims` 
WHERE 
claims.claim_id in (SELECT max(claim_id) from claims GROUP by claim_company_id)

The output should be

2 - 1 - 1/2/2010
5 - 7 - 1/5/2010

But when I run this query

$query = DB::table('claims')    
                    ->groupBy('claim_company_id')
                    ->get( ['claims.*','claim_company_id',DB::raw('MAX(claim_id) as claim_id_new')]);

The result is

1 - 1 - 1/1/2010
3 - 7 - 1/3/2010

Im stuck for a day now. Any Ideas?

  • 写回答

1条回答 默认 最新

  • doutuo1939 2017-10-30 10:13
    关注

    So I needed to use whereRaw to run the code but turns out it works this way. Answering for future reference

    $query = DB::table('claims') 
                 ->whereRaw('claim_id in (select max(claim_id) from claims group by (claim_company_id))')
                 ->get();
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?