Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – How to get latest prices for each product, from prices table

I’m using Laravel to build an online shop.
I have a table named ‘products’ and another table named ‘prices’.
each product can have numerous amounts for price (which means there could be numerous records for one product_id in prices table).
I need to get the one latest price for each product_id and then filter them to be between two specific amounts. Could someone please tell me how could I do It’s query?

Here is what I have done so far:

Product::whereHas('prices', function (Builder $query) {
            $query->groupBy('product_id')->whereBetween('amount', [0, 50000]);
        })->get();
Tags:

2

Answers


  1. 0

    Laravel supports adding a Relationship to access "One of Many", i.e. in your case, the "latest" Price from multiple associated prices. You should also be able to apply the price filtering directly within the method, or you can query on the fly via ->whereHas() (or ->withWhereHas()):

    You’d define something like this:

    public function latestPrice(): HasOne {
  return $this->hasOne(Price::class)
  ->whereBetween('amount', [0, 50000])
  ->latestOfMany();
}

    Then you’d use it as:

    $products = Product::with('latestPrice')->get();

    Or, to make the method more generic:

    public function latestPrice(): HasOne {
  return $this->hasOne(Price::class)->latestOfMany();
}

    And if you wanted to query for a specific range:

    $products = Product::with(['latestPrice' => function (Builder $query) {
  return $query->whereBetween('amount', [0, 50000]);
})->get();

    Sidenote, ->with() is for Eager Loading, ->whereHas() is used to filter for products that have a latestPrice, ->withWhereHas() is a combination; use the appropriate one for you use case.

    Full documentation can be found here:

    https://laravel.com/docs/10.x/eloquent-relationships#has-one-of-many

    Login or Signup to reply.
  2. 0 
    Product::whereHas('prices', function (Builder $query) {
  $query->select('product_id', DB::raw('MAX(created_at) as latest_price_date'))
  ->groupBy('product_id')
  ->whereBetween('amount', [0, 50000]);
})->with(['prices' => function ($query) {
  $query->orderBy('created_at', 'desc')->limit(1);
}])->get();

    Fetch Products with Latest Prices
    Product::whereHas(‘prices’): Fetches products that have at least one price within the specified range.
    select(‘product_id’, DB::raw(‘MAX(created_at) as latest_price_date’)): Selects the product ID and the latest price’s creation date for each product.
    groupBy(‘product_id’): Groups the prices by product ID, ensuring only one price per product is fetched.
    whereBetween(‘amount’, [0, 50000]): Filters prices based on the desired amount range.

    Eager Load Latest Price:
    with([‘prices’ => function ($query) { … }]): Eager loads the related prices relationship for each product.
    $query->orderBy(‘created_at’, ‘desc’)->limit(1): Orders the prices in descending order based on creation date and limits them to only the latest one

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search