CODEWITHSUNDEEP
phplaraveljoinpivot
Sharif
Sharif

Reputation: 107

Perform multiple pivot table join query perform SUM using Laravel Join

I have three table to join,

  1. products: id
  2. product_purchase: product_id, purchase_id, quantity, subtotal
  3. product_warehouse: product_id, purchase_id, quantity

Here products have multiple pivot tables, like product_purchase, product_warehouse. I want to perform a sum query with respect to each product id. The query I have written is,

Product::select('products.id', 'products.code')
        ->selectRaw('sum(product_warehouse.quantity) as remaining_quantity')
            ->leftjoin('product_warehouse', 'product_warehouse.product_id', '=', 'products.id')
            ->leftjoin('product_purchase', 'product_purchase.product_id', '=', 'products.id')
            ->groupBy( 'products.id' )
            ->fromCompany()
            ->get();

and the result is

[
{
id: 7,
code: "part1",
remaining_quantity: "8.000000000000000000"
},
{
id: 8,
code: "part2",
remaining_quantity: "6.000000000000000000"
},
{
id: 9,
code: "part-full",
remaining_quantity: "0.000000000000000000"
}
]

The remaining quantity sum is performing wrong. I am asking for help that where is wrong with the query I have written. Looking for your answer and comment.

Upvotes: 1

Views: 147

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35583

You should calculate the sum of quantity from the warehouses for each product before it is affected by the join with purchases. Then, also calculate the sum of quantity from the purchases for each product so that this isn't affected by a join to warehouses. Finally, left join both of these to the products table for the combined view.

$productWarehouseSubquery = DB::table('product_warehouse')
    ->select('product_id', DB::raw('SUM(quantity) as warehouse_quantity'))
    ->groupBy('product_id');

$productPurchaseSubquery = DB::table('product_purchase')
    ->select('product_id', DB::raw('SUM(quantity) as purchase_quantity'))
    ->groupBy('product_id');

$products = Product::select('products.id', 'products.code')
    ->selectRaw('COALESCE(warehouse_quantity, 0) - COALESCE(purchase_quantity, 0) as remaining_quantity')
    ->leftJoinSub($productWarehouseSubquery, 'product_warehouse', function ($join) {
        $join->on('product_warehouse.product_id', '=', 'products.id');
    })
    ->leftJoinSub($productPurchaseSubquery, 'product_purchase', function ($join) {
        $join->on('product_purchase.product_id', '=', 'products.id');
    })
    ->fromCompany()
    ->get();

This should give you the sum of quantity from both the product_warehouse and product_purchase tables for each product_id without the interference of row multiplications when you join all tables via a single query. So the remaining_quantity is calculated by subtracting the aggregated purchase_quantity from the aggregated warehouse_quantity.

Note, COALESCE is used to handle cases where there are no records in the product_warehouse or product_purchase tables for a given product_id.

Upvotes: 1

Related Questions