Get the count of a specific column with join query in Laravel-CodePudding

I have 2 tables, products and stocks. Each product has a stock record foreign key product_id in the stocks table. Each stock record has a quantity column.

Fields in the stocks table look like,

id | product_id | color_id | quantity

In brief: 1 product can have many stock records. I'm trying to get the product and its total count in the quantity column of stock records. Imagine product A (product id is 1) has 3 stock records in the stocks table like this,

id | product_id | color_id | quantity
1  |     1      |     3    |   10
2  |     1      |     4    |   20
3  |     1      |     2    |   40

Here for product A the total of quantity column in the stocks table is 10 20 40 = 70. I'm expecting to return this 70 and the product record.

Here is my code. I have no idea how to get that,

      return DB::table('products')
        ->join('stocks', 'products.id', '=', 'stocks.product_id')
        ->paginate(5);

CodePudding user response：

If your joins are correct use sum() with groupBy() clause

->select(DB::raw("SUM(stocks.quantity) as stocks_quantity"))
->groupBy('stocks.product_id') # or ->groupBy('products.id')

DB::table('products')
    ->join('stocks', 'products.id', '=', 'stocks.product_id')
    ->select(DB::raw("SUM(stocks.quantity) as stocks_quantity"))
    ->groupBy('products.id') #or groupBy('stocks.product_id')
    ->paginate(5);

Edit 01

DB::table('products')
    ->join('stocks', 'products.id', '=', 'stocks.product_id')
    ->select('products.*')
    ->selectRaw("SUM(stocks.quantity) as stocks_quantity")
    ->groupBy('stocks.product_id')
    ->get(5);

Edit 02

add 'products.id' in select

DB::table('products')
    ->leftjoin('stocks', 'products.id', '=', 'stocks.product_id')
    ->select('products.id','products.*')
    ->selectRaw("SUM(stocks.quantity) as stocks_quantity")
    ->groupBy('products.id')
    ->get(5);