Get the latest record from a selected group by id

Question

How can I convert the code below to a single query in eloquent ?

The table has the following columns ( id (primary index) - sku - serial_number - status - created_at - updated_at)

The aim is to retrieve the latest status 'latest record' of each product that shares the same SKU.

// get all the products that shares the same sku number
$list_of_products_status = Product_status::where('sku', 'sku-number')
    ->get();


$list_unique_serials = $list_of_products_status->pluck('serial_number')->toArray();

$products= collect([]);
foreach ($list_unique_serials as $serial)
{
// get the latest status of each product
    $product = Product_status::where('serial_number', $serial)
        ->latest()->first();
    $products[] = $product;
}

Try this ```$list_of_products_status = Product_status::query()->where('sku', 'sku-number')->latest()->groupBy('serial_number')->get();``` — Akbarali, Jul 28 '22 at 15:16
I have the error column "product_status.id" must appear in the GROUP BY clause or be used in an aggregate function — Marcos DaSilva, Jul 28 '22 at 15:21
This is a similar solution but for MySQL https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql — Marcos DaSilva, Jul 28 '22 at 15:34

score 0 · Answer 1 · answered Jul 28 '22 at 16:25

you can use this package to getting item for each serial_number :

https://github.com/staudenmeir/eloquent-eager-limit

you should add this trait in your Model :

use \Staudenmeir\EloquentEagerLimit\HasEagerLimit;

and your query like bellow :

     $products = Product_status::query()
        ->where('sku', 'sku-number')
        ->groupLimit(1, 'serial_number')
        ->get();

also you can change first parameter in groupLimit to get more product each group

just for more information:

this package generate this query:

select
    laravel_table.*,
    @laravel_row := if(
        @laravel_partition = `serial_number`,
        @laravel_row + 1, 1
      ) as laravel_row,
      @laravel_partition := `serial_number`
from
      (
        select
          @laravel_row := 0,
          @laravel_partition := 0
      ) as laravel_vars,
      (
        select
          *
        from
          `YOUR_TABLE_NAME`
        where
          `sku` = 'sku-number'
          and `YOUR_TABLE_NAME`.`deleted_at` is null
        order by
          `serial_number` Desc
      ) as laravel_table
having
      laravel_row <= 1
order by
      laravel_row Desc

and read discuss in laracasts:

https://laracasts.com/discuss/channels/laravel/selecting-n-rows-from-products-for-each-category

score 0 · Answer 2 · answered Jul 29 '22 at 03:49

0

The solution was much simpler than what I thought just writing this would suffice.

  $products = Product_status:::where('sku', 'sku number')
            ->distinct('serial_number')
            ->get();

answered Jul 29 '22 at 03:49

Marcos DaSilva

542
5
17

Get the latest record from a selected group by id

2 Answers2