Laravel多个连接计数(不雄辩)

Question

我将把这个Eloquent查询转换为Laravel中的DB查询。

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

上面这个是雄辩的，它工作得很好。但是我想用DB查询做到这一点。

我试着按照下面的方法去做，但是没有得到预期的结果。

$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();

addresses_count和cars_count的结果值相同，是两者的乘积。

如有任何帮助，不胜感激。

Answer 1

你只需要在count中添加distinct:

$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();