Get the latest record from a selected group by id-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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