Laravel - Elegant way to query the latest date on a database table

10,686

Solution 1

In order to apply scopes to a relationship, you have to call them on the relationship's method (rather than its dynamic property) to allow query chaining. Try this:

$product->prices()->latest()->amount

(Originally commented)

Solution 2

In your case it would be ideal to have an accessor:

public function getPrice()
{
    $this->prices()->sortBy('effective_datetime', 'desc')->first()->amount;
}

Now you can use it like $product->price.

Share:
10,686

Related videos on Youtube

Chris Landeza
Author by

Chris Landeza

Updated on September 15, 2022

Comments

  • Chris Landeza
    Chris Landeza about 1 year

    I have products and prices table (Temporal Data). what is the best approach for getting the latest price of a specific product? here's the basic structure of my two tables:

    products table:
    -ID
    -name
    
    prices table
    -ID
    -product_id
    -amount
    -effective_datetime
    

    Product Model:

    public function prices()
    {
        return $this->hasMany('App\Price', 'product_id', 'id');
    }
    

    Price Model:

    public function product()
    {
        return $this->belongsTo('App\Product', 'product_id');
    }
    

    Im currently using this code to get the latest price of a product:

     $product->prices->sortByDesc('effective_datetime')->first()->amount
    

    As you can imagine, I have to call that long line all over my application just to get the latest price of a product. is there a better way?

    My idea is to create a queryScope on my Price model like this:

    public function scopeLatest($query)
    {
            return $query->sortBy('effective_datetime', 'desc')->first();
    }
    

    and Call

    $product->prices->latest()->amount
    

    but laravel is throwing an error "Call to undefined method Illuminate\Database\Eloquent\Collection::latest()"

  • malhal
    malhal about 7 years
    Instead of sortBy you can use latest('effective_datetime')
  • Christhofer Natalius
    Christhofer Natalius over 4 years
    Tried accessor like this, added the column in protected $appends variable, and it shows result as expected. But, when I log the queries, it calls another query for each item, so when I call Item::paginate(25), it is doing 1 + 25 queries.