Laravel 4 Query Builder - groupBy Max Date

Question

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
-------------------------------

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 
-------------------------------

try `SELECT * FROM table group by user_id having(max(paper_update)) ` — Sam, Aug 17 '14 at 17:51
Unfortunately, I don't have DB installed here. You can try this select * from user where (parse_Date, user_id) = (select max(parse_date), user_id from user group by user_id) — CuriousMind, Aug 17 '14 at 18:30

Fasil kk · Answer 1 · 2015-07-03T10:07:47.767

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();

This laravel query will create a mysql query like this

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

The out put will be

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
        )
)

score 3 · Answer 2 · answered Aug 19 '14 at 07:33

MySQL :

You can use just a Left Join and avoid a Group By to retrieve the last paper_update for each user_id :

SELECT t1.*
FROM paper AS t1 LEFT JOIN paper AS t2
ON (t1.user_id = t2.user_id AND t1.paper_update < t2.paper_update)
WHERE t2.paper_update IS NULL;

Assuming your table is named paper.

SEE DEMO

Laravel Query Builder :

I'm not an expert with the Laravel Query Builder, but you can try this :

$result = DB::table('paper AS t1')
->select('t1.*')
->leftJoin('paper AS t2', function( $join ){
    $join->on( 't1.user_id', '=', 't2.user_id' );
    $join->on( 't1.paper_update', '<', 't2.paper_update' );
})
->whereNull('t2.paper_update')
->get();

score 2 · Answer 3 · answered Aug 17 '14 at 20:21

Not familiar with laravel's query builder but in plain Mysql you use a self join with the maximum date value from same table

select t.*
from t 
join (
  select `user_id`, max(`paper_update`)`paper_update`
  from t group by `user_id`
  ) t1
using(`user_id`, `paper_update`)

Laravel 4 Query Builder - groupBy Max Date

3 Answers3

`Fiddle Demo`

Linked