Use withCount() to Calculate Child Relationships Records in Laravel

On November 17, 2020 . By CodimTh

Category:

Use withCount() to Calculate Child Relationships Records

If you have hasMany() relationship, and you want to calculate “children” entries, don’t write a special query. For example, if you have posts and comments on your User model, write this withCount():


public function index()
{
 $users = User::withCount(['posts', 'comments'])->get();
 return view('users', compact('users'));
}

And then, in your Blade file, you will access those number with [relationship]_count
properties:


@foreach ($users as $user)
 <tr>
 <td>{{ $user->name }}</td>
 <td class="text-center">{{ $user->posts_count }}</td>
 <td class="text-center">{{ $user->comments_count }}</td>
 </tr>
@endforeach

In addition, using the loadCount method, you may load a relationship count after the parent model has already been retrieved:

$book = App\Models\Book::first();

$book->loadCount('genres');

If you need to set additional query constraints on the eager loading query, you may pass an array keyed by the relationships you wish to load. The array values should be Closure instances which receive the query builder instance:

$book->loadCount(['reviews' => function ($query) {
    $query->where('rating', 5);
}])

In addition to the withCount method, Eloquent provides withMin, withMax, withAvg, and withSum. These methods will place a {relation}_{function}_{column} column on your resulting models. For example:

$posts = App\Models\Post::withSum('comments', 'votes')->get();

foreach ($posts as $post) {
    echo $post->comments_sum_votes;
}

These additional aggregate operations may also be performed on Eloquent models that have already been retrieved:

$post = App\Models\Post::first();

$post->loadSum('comments', 'votes');

If you would like to eager load a morphTo relationship, as well as nested relationship counts on the various entities that may be returned by that relationship, you may use the with method in combination with the morphTo relationship's morphWithCount method.

In this example, let's assume Photo and Post models may create ActivityFeed models. Additionally, let's assume that Photo models are associated with Tag models, and Post models are associated with Comment models.

Using these model definitions and relationships, we may retrieve ActivityFeed model instances and eager load all parentable models and their respective nested relationship counts:

use Illuminate\Database\Eloquent\Relations\MorphTo;

$activities = ActivityFeed::query()
    ->with(['parentable' => function (MorphTo $morphTo) {
        $morphTo->morphWithCount([
            Photo::class => ['tags'],
            Post::class => ['comments'],
        ]);
    }])->get();

In addition, you may use the loadMorphCount method to eager load all nested relationship counts on the various entities of the polymorphic relation if the ActivityFeed models have already been retrieved:

$activities = ActivityFeed::with('parentable')
    ->get()
    ->loadMorphCount('parentable', [
        Photo::class => ['tags'],
        Post::class => ['comments'],
    ]);

Tags :

Laravel Laravel tutoriel Tips Laravel