Eloquent: Scope for Model where latest related Model meets permission

Question

I have a Model Products that has many of the Model Prices. Every day there is a different price for the product.

Now I am trying to create a scope that gives me all products which latest price is between two values.

I tried this with a whereHas query:

public function scopePriceBetween($query, ...$priceRange) {
  return $query->whereHas('price', function ($query) use ($priceRange) {
     $query->latestPrice()->whereBetween('price', $priceRange); 
  })
}

with the scope on the price model

public function scopeLatestPrice($query) {
  return $query->latest('date')->limit(1);
}

But this will give me all the products where any price was between the range and not just the latest price.

Is there a way to do this with acceptable performance in eloquent or do I need to add a latest_price column to my product model?

Answer 1

for later price you can use database temp column or you can use redis. but i recommend temp column.

First Solution : Temporary Table

DB::statement("CREATE TEMPORARY TABLE last_prices SELECT prices.* from prices join products on products.id=prices.product_id and prices.id=(select id from prices where prices.product_id=products.id and `prices`.`deleted_at` is null order by `id` desc limit 1);");
        $query = Product::select("products.*")
                 ->join("last_prices", "products.id", "last_prices.product_id");
        

in this example, every task has many jobs, you can query database to make a temporary table and fetch the last_job from jobs;

Second Solution : Using Cache Server

DBMS temp table is fast, but you can gain performance by Cache server (for example redis).

you can store every product last price in cache server by product_id:

  public function getLastPriceAttribute(){
    
    //cache for an hour
    $p_id = $this->id;
    return  Cache::tags(['product'])->remember($this->id, 60*60, function () uses ($p_id) {
            return Price::where('product_id', $p_id)
                ->latest()
                ->first();
        });  
}

The third solution:

if your price updates are daily and you haven't or don't want to use cache server you can make a database table named last_prices and update it daily with laravel schedule as follow:

in App\Console\Kernel.php :

//suggestion has not tested
protected function schedule(Schedule $schedule)
    {
        $schedule->call(function () {
            $updateValues = array();
            
            foreach( Product::all() as $product){
               array_push($updateValues , array(
                   "product_id" => product->id,
                   "price_value" => 
                   Price::where('product_id',$product->id)
                   ->latest()
                   ->first()->price_value;
                   ));
             }
        LastPrices::updateOrInsert($updateValues);
        })->dailyAt("05:30");        }

UPDATE

for this:

Product::latestPriceBetween([100,200])->category('electronics');

you can make Suggested Third Solution to have Last_price Table.

and define scope with join, with this nice package : https://github.com/fico7489/laravel-eloquent-join

looks like something like this:

public function scopePriceBetween($query, ...$priceRange) {
  return $query->join("last_prices","last_prices.product_id","products.id")->whereBetween('last_prices.value', $priceRange);
}