Laravel Multiple Joins with Counts (Not Eloquent)-CodePudding

I'm going to convert this Eloquent query to DB query in Laravel.

$users = User::withCount("addresses", "cars")->get();

The above one is Eloquent and it's working well. But I'd like to do this with DB query.

I tried to do as following but I couldn't get expected result.

$users = \DB::table("users")->join("addresses", "users.id", "=", "addresses.user_id")
->join("cars", "users.id", "=", "cars.user_id") 
->selectRaw("users.id, users.name, count(addresses.id) as addresses_count, count(cars.id) as cars_count")
->groupBy("users.id", "users.name")
->get();

The result value of addresses_count and cars_count were same and it was multiplied value of two.

Any help would be appreciated.

CodePudding user response：

you just need to add distinct in count like:

$users = \DB::table("users")->join("addresses", "users.id", "=", "addresses.user_id")
->join("cars", "users.id", "=", "cars.user_id") 
->selectRaw("users.id, users.name, count(distinct addresses.id) as addresses_count, count(distinct cars.id) as cars_count")
->groupBy("users.id", "users.name")
->get();