加入数据库和计数

Question

I'm trying to list informations about a table and one of that information is how much cars an user has. I have two databases, one is users and the other is cars. The table cars has a column that is owner that holds the id of the owner. What I want to know is: How to list all users and along with that the total of cars that each user has?

$users = Users::all();

This code returns an array with all users, what I want is to pass the total of cars that each user has on the same $users variable. How can I do that? Is there a way to join the other table, count and then return or something like that?

@edit

I tried like this, but doesn't work:

$users = Users::join('cars', 'cars.owner', '=', 'users.id')->select(DB::raw('count(cars.car_id) as total'))->get();

Answer 1

You need the group by statemant:

select users.name, count(*) as counter from users 
join cars on ... group by users.name;

Okay here in more Detail:

You have to join the users table with the cars table. You do that, yes.

Then you have to select one col from the user table and one count(*) as counter

The trick is now, to "group by" the col from the users table. That matches all double user rows to one row and count how much cars one user has

The select statement is:

"select users.id, count(*) as counter from Users join Cars on cars.owner=users.id group by users.id"

Thats all ... hope that help you