laravel join with only limiting 2 row

Question

so i having this kind of query where i do grouping with max date/period like this

$table_data = App\salesreport::join(DB::RAW('(SELECT company_id, MAX(period) AS max_period FROM salesreport GROUP BY company_id ) latest_report'),function($join){
            $join->on('salesreport.company_id','=','latest_report.company_id');
            $join->on('salesreport.periods','=','latest_report.max_periods');
    })->get()

and it work just fine, it can group all company into one and then showing only the latest one MAX(period). And then i want to tweak it more, how about if i want to show not only the latest one, but also the latest two of each company? so each company will returning 2 report that is the latest of all report.

and then i think maybe adding LIMIT 2 will make it works so i put it like this

$table_data = App\salesreport::join(DB::RAW('(SELECT company_id, MAX(period) AS max_period FROM salesreport GROUP BY company_id ) latest_report'),function($join){
            $join->on('salesreport.company_id','=','latest_report.company_id');
            $join->on('salesreport.periods','<=','latest_report.max_periods');
            $join->limit(2)
    })->get()

but it has no effect and just showing all report of all company which have period <= max_periods.

to make things more clearer here is my table that i do joining

so here is my sales report table

+----+------------+-------+------------+
| id | company_id | price |  periods   |
+----+------------+-------+------------+
|  1 | A1         |   500 | 2016-07-12 |
|  2 | A2         |   540 | 2017-01-21 |
|  3 | A1         |   440 | 2017-01-19 |
|  4 | A1         |   440 | 2018-01-19 |
|  5 | A2         |   330 | 2016-01-12 |
|  6 | A2         |   333 | 2018-01-22 |
+----+------------+-------+------------+

and then using the first query up there then i will get this kind of table

+----+------------+-------+------------+
| id | company_id | price |  periods   |
+----+------------+-------+------------+
|  4 | A1         |   440 | 2018-01-19 |
|  6 | A2         |   333 | 2018-01-22 |
+----+------------+-------+------------+

so what i want is to get this kind of table

+----+------------+-------+------------+
| id | company_id | price |  periods   |
+----+------------+-------+------------+
|  4 | A1         |   440 | 2018-01-19 |
|  3 | A1         |   440 | 2017-01-19 |
|  6 | A2         |   333 | 2018-01-22 |
|  2 | A2         |   540 | 2017-01-21 |
+----+------------+-------+------------+

so the latest 2 of all for each company.. is it possible? with only one request?

i can't comment on how to transform into eloquent, however the query basic structure can be found here. https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results - maybe someone could adapt this for you — Juakali92, Mar 23 '18 at 14:37
wow okay it's quite alot to chew in... and it is a very advance query that i ever encounter — PamanBeruang, Mar 23 '18 at 15:16
it's a very particular use case, something i would of probably done using 2 queries using an offset and limit on the second and merging both result sets together. But if you're up for the challenge! — Juakali92, Mar 23 '18 at 15:33
yea at past i using 2 set of queries and doing foreach for the second queries, very bad in performance but get things done... so right now i like want to fix those but... man... i am having hard time turn that query into laravel there is many things i still don't understand from those queries — PamanBeruang, Mar 23 '18 at 15:45
It will definitely be a great leaning exercise into not only different concepts and methods of eloquent, but also advanced SQL. I would give it a try and see what you can come up with. — Juakali92, Mar 23 '18 at 15:49

score 0 · Accepted Answer · answered Mar 23 '18 at 16:07

Using this trick:

App\salesreport::join(DB::RAW('(SELECT company_id, GROUP_CONCAT(periods ORDER BY periods DESC) grouped_periods FROM salesreport GROUP BY company_id ) latest_report'),function($join){
        $join->on('salesreport.company_id','=','latest_report.company_id');
        $join->whereBetween(DB::raw('FIND_IN_SET(`salesreport`.`periods`, `latest_report`.`grouped_periods`)'), [1, 2]);
    })->get();

Ravindra Bhanderi · Answer 2 · 2020-12-22T04:37:28.333

-2

Using this trick:

App\salesreport::join(DB::RAW('(SELECT company_id, GROUP_CONCAT(periods ORDER BY periods DESC) grouped_periods FROM salesreport GROUP BY company_id ) latest_report'),function($join){
        $join->on('salesreport.company_id','=','latest_report.company_id');
        $join->whereBetween(DB::raw('FIND_IN_SET(`salesreport`.`periods`, `latest_report`.`grouped_periods`)'), [1, 2]);
    })->get();

edited Dec 22 '20 at 04:37

answered Mar 23 '18 at 17:04

Ravindra Bhanderi

2,478
4
17
29

laravel join with only limiting 2 row

2 Answers2

Linked