Laravel 4查询生成器-groupBy最大日期 [英] Laravel 4 Query Builder - groupBy Max Date

问题描述

小查询问题， 我有桌子:

little query question, I have table:

id    | user_id | paper_update
------------------------------
1     |    1    | 30-5-2011
2     |    2    | 30-5-2012
3     |    3    | 30-5-2012   
4     |    1    | 30-5-2013  
5     |    2    | 30-5-2013  
6     |    3    | 30-5-2014  
7     |    4    | 30-5-2014  
8     |    5    | 30-5-2014
9     |    5    | 30-5-2015 
10    |    5    | 30-5-2016
11    |    1    | 30-5-2010
-------------------------------

我想要做的是仅选择具有相同user_id的记录之间paper_update最大的记录，实际上我想按user_id分组以便获得最大的paper_update. 因此对于此表，它将返回以下内容:

What I'm looking to do is to select only the records where paper_update is max between records with the same user_id, actually I want to group by the user_id in order to the max paper_update. so for this table it will return this:

id    | user_id | paper_update
------------------------------
4     |    1    | 30-5-2013  
5     |    2    | 30-5-2013  
6     |    3    | 30-5-2014  
7     |    4    | 30-5-2014  
10    |    5    | 30-5-2016 
-------------------------------

推荐答案

这是在laravel中获取groupBy Max Date的最简单，最短的方法.将最大日期的行与user_id分组.

This is the easiest and shortest way to get groupBy Max Date in laravel. Take the rows with max date with group by user_id.

       $rows = DB::table('papers')
               ->select(DB::raw('id, max(paper_update) as year,user_id'))
               ->groupBy('user_id')
               //->orderBy('paper_update', 'desc')
               ->get();

这个laravel查询将创建一个像这样的mysql查询

This laravel query will create a mysql query like this

select id, max(paper_update) as year from `papers` group by `user_id`;

产量将

Array
(
    [0] => stdClass Object
        (
            [id] => 4
            [year] => 30-5-2013 
            [user_id] => 1
        )

    [1] => stdClass Object
        (
            [id] => 5
            [year] => 30-5-2013
            [user_id] => 2
        )

    [2] => stdClass Object
        (
            [id] => 6
            [year] => 30-5-2014
            [user_id] => 3
        )

    [3] => stdClass Object
        (
            [id] => 7
            [year] => 30-5-2014
            [user_id] => 4
        )
    [4] => stdClass Object
        (
            [id] => 10
            [year] => 30-5-2016
            [user_id] => 5
        )
)

这篇关于Laravel 4查询生成器-groupBy最大日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！