CODEWITHSUNDEEP
phplaraveleloquent
Tobias Krischer
Tobias Krischer

Reputation: 384

Eloquent Relationship Count

I have a problem with Laravel 4 and Elqouent. My Models have the following relationships implemented:

User belongsToMany Role
Role belongsToMany User

I want to query for the roles and want to get the count of related users with them.

When I do the following, I have the N+1 problem:

$role = Role::has('users')->get()->each(function($role) {
    $role->user_count = $role->users()->count();
});

Is there anything I did not think about?

Upvotes: 0

Views: 1010

Answers (3)

Felipe Castillo
Felipe Castillo

Reputation: 577

Use withCount()

  $companies = $this->company
                        ->with("municipality.province")
                        ->withCount("projects","users")
                        ->get();

this return

{
        "id": 1,
        "name": "TEST",
        "fiscal_id": "0000000",
        "image_path": "/assets/icons/svg/energy.svg",
        "municipality_id": 1111,
        "telephone": null,
        "is_active": true,
        "sector": "consultancy",
        "type": "legal",
        "created_at": "2022-07-16T16:05:38.000000Z",
        "updated_at": "2022-07-16T16:05:38.000000Z",
        "projects_count": 2,
        "users_count": 2,
        "image_src": "http://localhost//assets/icons/svg/energy.svg"
    }

Upvotes: 0

Tobias Krischer
Tobias Krischer

Reputation: 384

Despite my appreciation of you guys helping me, neither of your solutions actually did the trick unfortunately.

I have found another question+answer that helped me, though.

Here is the solution that is perfectly working what I wanted:

Role::join('assigned_roles', 'assigned_roles.role_id', '=', 'roles.id')
        ->groupBy('roles.id')
        ->orderBy('user_count', 'desc')
        ->orderBy('name', 'asc')
        ->get(['roles.*', DB::raw('COUNT(assigned_roles.id) as user_count')]);

Upvotes: 1

Jarek Tkaczyk
Jarek Tkaczyk

Reputation: 81187

This is what you need for belongsToMany:

// Role model

// helper relation
public function usersCount()
{
    return $this->belongsToMany('User')
      ->selectRaw('count(users.id) as count')
      ->groupBy('pivot_role_id');
}

// accessor for $role->usersCount;
public function getUsersCountAttribute()
{
    if ( ! array_key_exists('usersCount', $this->relations)) $this->load('usersCount');

    $related = $this->getRelation('usersCount')->first();

    return ($related) ? $related->count : 0;
}

Then you can eager load it:

$roles = Role::with('usersCount')->get();

// pick one, for example
$roles->first()->usersCount; // int
$roles->last()->usersCount; // 0 if no related users were found

You current solution unfortunately is pretty bad, because it queries the db for each role.

The code above lets you fetch that count for all the roles with just a single query. Another thing is that you don't load all the related users if not necessary, so it doesn't eat your memory (in case it would matter).

Upvotes: 1

Related Questions