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

Question

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()"

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.

Comments

[malhal]

Instead of sortBy you can use latest('effective_datetime')

[Christhofer Natalius]

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.