CODEWITHSUNDEEP
sqllaravel
davidkihara
davidkihara

Reputation: 533

How do I join with count() in Laravel?

I have two tables shops and products. I can get all shops but want to join so that I can know how many products aa shop has. I have tried this but it returns an empty table.

$shops = DB::table('shops')
            ->select('*','COUNT(shop_id) FROM products')
            ->join('shops.id','=','products.shop_id')
            ->get();

I have tried using SQL but return one shop and count of all products

SELECT * FROM `shops` JOIN (SELECT shop_id, COUNT(shop_id) FROM products AS KK) AS KKK on shops.id = KKK.shop_id

How do I change the query so that I get all shops and number of products each shop has?

Upvotes: 1

Views: 230

Answers (2)

KUMAR
KUMAR

Reputation: 1995

Try this:-

$shops = DB::table('shops')
        ->join('products', 'products.shop_id', '=', 'shops.id')
        ->select('shops.id as id', DB::raw("count(products.id) as count"))
        ->groupBy('shops.id')
        ->get();

Upvotes: 1

Odin Thunder
Odin Thunder

Reputation: 3547

If you wrote proper Laravel Relations between this two tables, you can use withCount() method:

Shop::withCount('products')->get();

But if you wanna use join:

\DB::table('shops')->join('products', 'products.shop_id', '=', 'shops.id')
->select('shops.*', DB::raw('count(products.id) as KK'))
->groupBy('shops.id')
->get()

Upvotes: 3

Related Questions