Laravel count roles of user based on another table

I have a table of Employee which has organisation column with different names, each organization has employee and employer role, i want to fetch organization name and count total number of employee and employer

like that with keys

['organisation' => 'UAE Hospital'
'Employee' => '10'
'Employer' => '5']

i have try this so far but it shows N+1 Query.

$data = Employee::where("organisation", 'UAE Hospital')
            ->select('organisation')
            ->whereHas("user.roles", function ($query) {
                $query->where("id", '2');
            })
            ->OrWhereHas("user.roles", function ($query) {
                $query->where("id", '3');
            })
            ->get()->toArray();

For roles i am using spatie package.

kokoshneta

3 years ago

Level 27

The organisation column is just a string? That’s bad database design. You should make an organisations table with IDs and names of each organisation, and then refer to the ID in your employee table? That would be the standard way to do it.

You’d then have HasMany relationships of employees and employers on the Organisation model, and you can eager load those with counts:

// Organisation model
public function employees() {
	return $this->hasMany(Employee::class);
}

public function employers() {
	return $this->hasMany(Employer::class);
}


// Employee model
public function scopeByRole(Builder $builder, $roles) {
	if (!is_iterable($roles)) $roles = [$roles];
	$builder->whereHas('user.roles' function ($query) use ($roles) {
		$query->whereIn('id', $roles);
	});
}

// Controller
$data = Organisation::select('name')
	->withCount(['employers', 'employees' => function ($query) {
		$query->byRole([2, 3]);
	})
	->get()
;

// Should output roughly
[
	'name' => 'Organisation ABC',
	'employers_count' => 10,
	'employees_count' => 15
]

(Untested, quick’n’dirty, but should more or less do the trick.)

Please or to participate in this conversation.