I'm new to the subject, I have two tables related to each other, one for departments and one for users, I want to know how many users there are in each department

DepartmentController.php

public function index()
{
    return Inertia::render('back/app/departments/index', [
        'filters'       => request()->all('visibility', 'status', 'search'),
        'departments'   => Department::orderBy('name')->get(),
        'total_members' => User::where('department_id')->count(),

        dd(User::where('department_id')->count()),
    ]);
}

Index.vue

<td class="flex items-center text-sm leading-5 text-gray-500 px-6 py-4 whitespace-no-wrap">
    <span class="font-medium">{{ department.total_members }}</span>&nbsp;{{ $trans('labels.members') }}
</td>
<td class="px-6 py-4 whitespace-no-wrap text-right text-sm leading-5 font-medium">
    <inertia-link :href="route('app:projectdepartment.index', {id: department.id})">
        {{ $trans('labels.view-activity') }}
    </inertia-link>
</td>

CodePudding user response：

If you have the users relationship set up on your Department model, you could use the withCount() method:

Department::withCount('users')->orderBy('name')->get()

This will give you a users_count property on each department.

CodePudding user response：

Assuming you have the relationships defined on each model as noted, you could also define an accessor or as was already stated get the number of related models by way of withCount or also with loadCount after retrieving an individual Department model.

Models/User.php

  public function department()
  {
    return $this->belongsTo(Department::class, 'department_id');
  }

  public function getActiveUserCountAttribute($value)
  {
    // Perform additional logic on the users relationship
    return $this->users()->where('active',1)->count();
  }

Models/Department.php

  public function users()
  {
    return $this->hasMany(User::class);
    
  }

Remember that one of the benefits of withCount is that it doesn't load the related models, avoiding an n 1 problem. If you use withCount, you wouldn't need the accessor defined on the Department model but would need the User relationship defined on Department.

Note that you could also use the loadCount method after you have already retrieved the Department model, e.g. for an individual show page as opposed to a collection.

// From your show method in your controller

$department = Department::where('name',$request['name'])->first();
$department->loadCount('users');

To eager load the 'active_user_count' attribute on each Department model, you would use the $with property. With this defined, you would not need to use the withCount or similar in your controller method.

// Department.php

// A relationship that should always be loaded with every Department request.
protected $with = ['active_user_count']

Laravel docs: https://laravel.com/docs/8.x/eloquent-relationships#counting-related-models