Level 26
You should be able to do something like this:
...
->leftJoin('domain_user', 'domains.id', '=', 'domain_user.domain_id')
->select(DB::raw('COUNT(domain_user.user_id)'))
....
Nov 28, 2021
5
Level 5
count from pivot table using query builder
I have this eloquent query:
$domains = Domain::with('users')
->withCount('contracts')
->withCount('users')
->orderBy('id', 'DESC')
->paginate(10)
->through(fn($domain) => [
'id' => $domain->id,
'name' => $domain->name,
'numberOfContracts' => $domain->contracts_count,
'numberOfUsers' => $domain->users_count,
'userIds' => $domain->users->pluck('id')->toArray()
]);
Important in this query is those 2
'numberOfUsers' => $domain->users_count,
'userIds' => $domain->users->pluck('id')->toArray()
The relations are:
- User hasMany Contracts
- Contract belongsTo User
- User belongsToMany Domains
- Domain belongsToMany Users
I want to replace the eloquent query from above with query builder:
$domains = DB::table('domains')
->leftJoin('contracts', 'domains.id','=','contracts.domain_id')
->select('domains.id', DB::raw('COUNT(contracts.domain_id) as numberOfContracts'), 'domains.name')
->groupBy('domains.id')
->where($filterService['filterCondition'])
->orderBy($filterService['sortBy'], $filterService['sortType'])
->paginate($filterService['items'])
->appends($filterService['queries']);
How can I get numberOfUsers (manyToMany) and userIds (the id associated for each domain) using the query builder ? Probably a callback function needed .... but not sure how....
Level 5
@idew I have allready tried that for counting the users
$domains = DB::table('domains')
->leftJoin('contracts', 'domains.id','=','contracts.domain_id')
->leftJoin('user_domains', 'domains.id', '=', 'user_domains.domain_id')
->select('domains.id', DB::raw('COUNT(contracts.domain_id) as numberOfContracts'), 'domains.name', DB::raw('COUNT(user_domains.user_id) as numberOfUsers'))
->groupBy('domains.id')
->where($filterService['filterCondition'])
->orderBy($filterService['sortBy'], $filterService['sortType'])
->paginate($filterService['items'])
->appends($filterService['queries']);
but it's not getting the correct values (numberOfUsers)
Ex: With model eloquent:
"id" => 24
"name" => "Architecto sapiente"
"numberOfContracts" => 7
"numberOfUsers" => 3
"userIds" => array:3 [
0 => 63
1 => 73
2 => 81
]
and with query builder DB
+"id": 24
+"numberOfContracts": 21
+"name": "Architecto sapiente"
+"numberOfUsers": 21
as you can see there should be 3 users not 21
Level 26
@calin.ionut Could it be that the raw SQL should be COUNT(DISTINCT user_domains.user_id)? This would be necessary if there are duplicates in the pivot table.
Level 5
@idew yup.....forgot about distinct. Now it shows correct. But what about the second ? How to get also the userIds ?
1 like
Level 26
@calin.ionut A group concat should do the trick - DB::raw('GROUP_CONCAT(DISTINCT user_domains.user_id) as userIds') then you'll need to map over them and explode the comma separated list to get an array.
Please or to participate in this conversation.