Laravel Eloquent/DB select top 1 Row Per Group

Question

I have a SQL Query in my controller, It fetches all data from a table to sort it by date. There are some duplicate entries of sku_parent in table with different id, I want to fetch the latest entry of each sku_parent according to 'date'. This is my DB:

+----+-----------+------------+-----------+--------------+------------+---------------------+---------------------+
| id | warehouse | sku_parent | sku_child | case_balance |    date    |     created_at      |     updated_at      |
+----+-----------+------------+-----------+--------------+------------+---------------------+---------------------+
|  5 | SeaWest   |        120 | 120 - 25  |          400 | 2020-08-26 | 2020-08-26 19:02:20 | 2020-08-26 19:02:20 |
|  6 | SeaWest   |        121 | 121 - 25  |          784 | 2020-08-26 | 2020-08-26 19:02:42 | 2020-08-26 19:02:42 |
|  7 | SeaWest   |        121 | 121 - 25  |          734 | 2020-08-26 | 2020-08-26 19:03:46 | 2020-08-26 19:03:46 |
|  8 | SeaWest   |        120 | 120 - 25  |          350 | 2020-08-26 | 2020-08-26 19:03:46 | 2020-08-26 19:03:46 |
+----+-----------+------------+-----------+--------------+------------+---------------------+---------------------+

This is what I have done so far:

    $data = DB::table('logs')
           ->where('sku_parent', $request->sku)
           ->where('id', \DB::raw("(select max(`id`) from logs)"))
           ->whereBetween('date', array($request->from_date, $request->to_date))
           ->get();

This code only fetches the max id from table and does not shows other product name. I want to get each product name having the max id.

By works presumably you mean just returns two rows, because it is not returning sensible data. Your first row shows an ID of 8, and a case_balance of 400, but the case_balance for the row with the id of 8 is in fact 350. Your question remains valid, i.e. how do I get the top 1 per group in laravel (which I don't know, I have never used it), but you definitely should not be trying to recreate that query. I've previously [explained with an example](https://stackoverflow.com/a/21680021/1048425) why (unless you know what you are doing) you should avoid this MySQL extension of the group by clause — GarethD, Aug 27 '20 at 17:04
thanks for mentioning that, i won't use that query, any way to get the data I want btw? — Kundan Nasir, Aug 27 '20 at 17:07
I think the following query would work for you: `SELECT ID, sku_parent, sku_child, case_balance , date , created_at, updated_at FROM logs WHERE ID IN (SELECT MAX(ID) FROM logs GROUP BY sku_parent)`, as I say though, I have no experience of Laravel, so can't translate this for you. The general principal would be to get the [top 1 per group](https://stackoverflow.com/search?q=Laravel+top+1+per+group), so there may be answers based on that search? — GarethD, Aug 27 '20 at 17:11
yes, the query is working fine, now just need to implement this in laravel :) — Kundan Nasir, Aug 27 '20 at 17:13
See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Aug 27 '20 at 21:24

zlatan · Answer 1 · 2020-08-27T20:22:59.280

0

Instead of searching MAX id of an item, you can group them all by the sku_parent first, and only take the latest records that have duplicate values with havingRaw. Try something like this:

$data = DB::table('logs')
       ->where('sku_parent', $request->sku)
       ->groupBy('sku_parent')
       ->havingRaw('count('sku_parent') > 1')
       ->whereBetween('date', array($request->from_date, $request->to_date))
       ->latest()
       ->get();

PS: Haven't tested it, but should give the desired result. Hit me up if any errors pop up, or if isn't what you wanted.

edited Aug 27 '20 at 20:22

answered Aug 27 '20 at 20:10

zlatan

3,346
2
17
35

syntax error, unexpected 'sku_parent' (T_STRING), expecting ')' – Kundan Nasir Aug 28 '20 at 16:39

score 0 · Answer 2 · answered Aug 28 '20 at 19:55

To pick a latest row per group (sku_parent) you can do self join like

Schema

CREATE TABLE logs
    (`id` int, `warehouse` varchar(7), `sku_parent` int, `sku_child` varchar(8), `case_balance` int, `date` datetime, `created_at` datetime, `updated_at` datetime)
;
    
INSERT INTO logs
    (`id`, `warehouse`, `sku_parent`, `sku_child`, `case_balance`, `date`, `created_at`, `updated_at`)
VALUES
    (5, 'SeaWest', 120, '120 - 25', 400, '2020-08-26 00:00:00', '2020-08-26 19:02:20', '2020-08-26 19:02:20'),
    (6, 'SeaWest', 121, '121 - 25', 784, '2020-08-26 00:00:00', '2020-08-26 19:02:42', '2020-08-26 19:02:42'),
    (7, 'SeaWest', 121, '121 - 25', 734, '2020-08-26 00:00:00', '2020-08-26 19:03:46', '2020-08-26 19:03:46'),
    (8, 'SeaWest', 120, '120 - 25', 350, '2020-08-26 00:00:00', '2020-08-26 19:03:46', '2020-08-26 19:03:46')
;

Query

select a.*
from logs a
left join logs b on a.sku_parent = b.sku_parent
    and a.id < b.id
where b.sku_parent is null
    and a.sku_parent = :sku_parent
    and a.date between :from_date and :to_date

Query Builder

DB::table('logs as a')
  ->select('a.*')
  ->leftJoin('logs as b', function ($join) {
        $join->on('a.sku_parent', '=', 'b.sku_parent')
             ->whereRaw(DB::raw('a.id < b.id'));
   })
  ->whereNull('b.sku_parent')
  ->where('a.sku_parent', $request->sku)
  ->whereBetween('a.date', array($request->from_date, $request->to_date))
  ->get();

If you need to pick the latest record base on created_at then change the leftJoin part to ->whereRaw(DB::raw('a.created_at < b.created_at'))

DEMO

Laravel Eloquent select all rows with max created_at

Laravel Eloquent/DB select top 1 Row Per Group

2 Answers2