CODEWITHSUNDEEP
phpmysqllaravellaravel-4eloquent
yayuj
yayuj

Reputation: 2464

Join database and count

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

Upvotes: 0

Views: 119

Answers (3)

Ali Gajani
Ali Gajani

Reputation: 15091

You can try this SQL query in raw. I tried and it works.

SELECT users.name as User_Name, COUNT(cars.user_id) as Car_Count
FROM users
LEFT JOIN cars
ON users.id=cars.user_id
GROUP BY users.id

enter image description here

Upvotes: 1

NULL
NULL

Reputation: 1858

I think this will help you...

Users::with([
              'cars' => function($q){
                           $q->select([DB::raw("count(car_id) as total"), "car_id"])
                             ->groupBy('car_id');
                        }
           ])->get();

Upvotes: 1

Tobi Klostermair
Tobi Klostermair

Reputation: 205

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

Upvotes: 2

Related Questions