Laravel - Eager Loading

Question

I'm trying to understand Eager Loading using Laravel to avoid generating a lot of unnecessary queries. I want to get 15 last added Posts and also get their rates from relationship of my rates table (before I was getting Posts and later in foreach I was calling for $item->avgRate() that creates 15 additional queries :S).

My Post model:

public function rates()
{
    return $this->hasMany(Rate::class);
}

public function scopeLastAdded($query, $limit = 15)
{
    return $query->latest()->limit($limit)->with('rates')->get();
}

This works, for each post, I'm also getting all rates, but the main goal is to make some function to calculate avg rate for each post and not retrieve all rates. I created a new method:

public function avgRate()
{
    return number_format($this->rates()->avg('rate'), 1, '.', '');
}

When I use with('avgRate') my model fails:

Call to a member function addEagerConstraints() on string

How can I get avgRate in some clean way with my last 15 Posts to perform only 2 queries and not 16?

Expected output:

// Post view
@foreach ($posts as $post)
   <div>{{ $post->title }}</div>
   <div>{{ $post->avgRate }}</div> //I want to get data without performing 15 queries
@endforeach

Answer 1

I would use a subquery to achieve this. Also, to make things a little bit cleaner, you can create a scope for fetching the rating:

public function scopeWithRating($query)
{
    $rating = Rate::selectRaw('AVG(rate)')
        ->whereColumn('post_id', 'posts.id')
        ->getQuery();

    $query->select('posts.*')
        ->selectSub($rating, 'rating');
}

... and to use it, you'd do:

Post::withRating()->get(); 

Now, your Post objects will also contain a column rating, and that has been done with, essentially, a single query.

Here's an example to illustrate this.