Laravel relations: retrieve count of one to many

Question

I have a case where I want to load all elements from one table (all my users), and see how ma ny phones they have. I know it's possible to load all phones, but this will result in too big querys. How can I just load the number of phones and not their content.

My current code (working, but retrieves too big results)

$users= User::get();
$users->load('phones');

I don't want to do a foreach as I don't want to have a N+1 select issue

I don't want to count the array in PHP as it is not optimal (retrieving everything from the DB to just do the count)

None of the already asked questions is satisfactory:

Laravel eloquent counting a relation uses n+1 querys
laravel 4 eloquent eager load relation count doesn't work at all

score 2 · Accepted Answer · answered Mar 13 '14 at 12:11

You may try something like this, assumed relation is made using user.id (PK) & phones.user_id:

$users = User::join('phones', 'users.id', '=', 'phones.user_id')
             ->groupBy('phones.user_id')
             ->select('users.*', 'phones.*', DB::raw('count(phones.user_id) countOfPhones'))
             ->get();

// Check the output
dd($users->toArray());

This will give you something like this (I've posts joined in the example):

array (size=4)
    0 => 
      array (size=14)
        'id' => int 1
        'first_name' => string 'Sheikh' (length=6)
        // ...
        'countOfPosts' => int 7 // <-- count of phones in your case
    1 => 
      array (size=14)
        'id' => int 2
        'first_name' => string 'Muhammed' (length=8)
        'last_name' => string 'Usman' (length=5)
        // ...
        'countOfPosts' => int 2 <-- count of phones in your case

So, use $users->first()->countOfPhones for first user's count of phones or loop all users.

Laravel relations: retrieve count of one to many

1 Answers1