Laravel - An elegant way to query for the latest date in a database table

Question

I have a table of products and prices (temporary data). What is the best approach to get the latest price for a particular product? here is 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'); } 

I am currently using this code to get the latest product price:

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

As you can imagine, I have to name this long line throughout my application in order to get the latest product price. is there a better way?

My idea is to create a Scope request on my price model as follows:

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

and challenge

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

but laravel throws the error "Calling the undefined method Illuminate \ Database \ Eloquent \ Collection :: latest ()"

Answer 1

To apply bindings to relationships, you must call them by the method of relations (and not by its dynamic property) in order to allow the query chain. Try the following:

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

(Originally commented)

Answer 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 as $product->price .