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


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:


(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.


Related videos on Youtube

Chris Landeza
Author by

Chris Landeza

Updated on September 15, 2022


  • 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:
    prices table

    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:


    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


    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.