Level 102
Can you show the generated query?
Aug 10, 2022
7
Level 4
addSelect the count of multiple rows to a query
Hello there, I'm trying to retrieve all clients and the count of the location for each client using the Database query builder. One-to-many relation between the client and locations. I'm doing this:
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\DB;
DB::table('clients')
->addSelect(['client' => function (Builder $builder) {
$builder->select('clients.name as client');
}])->groupBy('client')
->addSelect(['location' => function (Builder $builder) {
$builder->from('locations')->selectRaw('count(*) as location');
}]);
this gives me the following results:
array:2 [▼
0 => {#1560 ▼
+"id": 4
+"name": "Ahrens AG & Co. KGaA"
+"created_at": "2022-08-10 06:19:00"
+"updated_at": "2022-08-10 06:19:00"
+"client": "Ahrens AG & Co. KGaA"
+"location": 3714
}
1 => {#1556 ▼
+"id": 11
+"name": "Bachmann Römer GmbH"
+"created_at": "2022-08-10 06:19:00"
+"updated_at": "2022-08-10 06:19:00"
+"client": "Bachmann Römer GmbH"
+"location": 3714
}
]
The problem is the count of the location is not correct, I'm getting the count of all the locations in the database and not the locations of the client. A help from you will be appreciated.
I know I can use something like: Client::withCount('locations')->get() but what would be something like the withCount() method in the Database query builder world
Level 4
select
`clients`.*,
(
select
`clients`.`name` as `client`
) as `client`,
(
select
count(*) as location
from
`locations`
) as `location`
from
`clients`
group by
`client`
This is the generated query. I edited the query above and add the grouping which I forgot the first time I wrote this
Level 102
@Abdulsalam Try adding something like this
->addSelect(['location' => function (Builder $builder) {
$builder->from('locations')->selectRaw('count(*) as location')->whereColumn('locations.id', 'clients.location_id');
}]);
Level 4
@Sinnbeck The Client has many Locations:
class Client extends Model
{
use HasFactory;
protected $guarded = [];
public function locations()
{
return $this->hasMany(Location::class);
}
}
class Location extends Model
{
use HasFactory;
protected $guarded = [];
public function client()
{
return $this->belongsTo(Client::class);
}
}
I've tried what you said it produced an error and I tried the following:
->addSelect(['location' => function (Builder $builder) {
$builder->from('locations')->selectRaw('count(*) as location')->whereColumn('clients..id', 'locations.client_id');
}]);
this is the generated new query:
select
`clients`.*,
(
select
`clients`.`name` as `client`
) as `client`,
(
select
count(*) as location
from
`locations`
where
`clients`.``.`id` = `locations`.`client_id`
) as `location`
from
`clients`
group by
`client`
and I got 1 location for the client:
0 => {#1560 ▼
+"id": 4
+"name": "Ahrens AG & Co. KGaA"
+"created_at": "2022-08-10 06:19:00"
+"updated_at": "2022-08-10 06:19:00"
+"client": "Ahrens AG & Co. KGaA"
+"location": 1
}
1 => {#1554 ▼
+"id": 11
+"name": "Bachmann Römer GmbH"
+"created_at": "2022-08-10 06:19:00"
+"updated_at": "2022-08-10 06:19:00"
+"client": "Bachmann Römer GmbH"
+"location": 1
}
Which is not correct because every client has over 500 locations. Any suggestion?
Level 102
@Abdulsalam You have 2 dots in 'clients.id'
->addSelect(['location' => function (Builder $builder) {
$builder->from('locations')->selectRaw('count(*) as location')->whereColumn('clients.id', 'locations.client_id');
}]);
1 like
Level 4
@Sinnbeck thank you very much. You saved my day!!!
Level 102
@Abdulsalam Great to hear :) Happy to help
Please or to participate in this conversation.