CODEWITHSUNDEEP
mysqllaraveleloquent
knubbe
knubbe

Reputation: 1182

How to substract two sums of columns from two different joins in laravel?

I have an articles table and I want to join two different tables where I track income and outcome articles. I want to substract sum of quantities from one table with sum of quantities from another table. This is what I try but I always get null in quantity.

return DB::table('articles')
            ->leftJoin('article_incoming_document', function ($join) use ($warehouse, $warehouseType) {
                $join->on('articles.id', '=', 'article_incoming_document.article_id')
                    ->where('article_incoming_document.warehouse', '=', $warehouse)
                    ->where('article_incoming_document.warehouse_type', '=', $warehouseType);
            })
            ->leftJoin('article_outgoing_document', function ($join) use ($warehouse, $warehouseType) {
                $join->on('articles.id', '=', 'article_outgoing_document.article_id')
                    ->where('article_outgoing_document.warehouse', '=', $warehouse)
                    ->where('article_outgoing_document.warehouse_type', '=', $warehouseType);
            })
            ->select([
                'articles.id',
                'articles.name',
                DB::raw('(SUM(article_incoming_document.quantity) - SUM(article_outgoing_document.quantity)) as quantity'),
            ])
            ->where('quantity', '>', 0)
            ->groupBy('articles.id');

Upvotes: 1

Views: 1078

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

Using multiple joins in query will result in multiple rows for example if one article has 2 rows from incoming table 2 or more related rows from outgoing table which will produce incorrect sum value. To handle such type of issue you can use sub clauses for your related tables (incoming and outgoing) and calculate sum for each article in their respective sub cause. After that join these sub clauses with your article table and subtract their quantity.

In plain SQL it would look like

select  a.id,
        a.name,
        coalesce(i.quantity,0) -  coalesce(o.quantity,0) as quantity
from articles as a
left join (
    select article_id,sum(quantity) as quantity
    from article_incoming_document
    where   warehous = :warehouse
        and warehouse_type: warehouse_type
    group by article_id
) i on a.id = i.article_id
left join (
    select article_id,sum(quantity) as quantity
    from article_outgoing_document
    where   warehous = :warehouse
        and warehouse_type: warehouse_type
    group by article_id
) o on a.id = o.article_id
having quantity > 0 // or where (i.quantity - o.quantity) > 0

In newer versions of laravel you can use leftJoinSub

$articleIncoming = DB::table('article_incoming_document')
                   ->select('article_id', DB::raw('sum(quantity) as quantity'))
                   ->where('warehouse', $warehouse)
                   ->where('warehouse_type', '=', $warehouseType)
                   ->groupBy('article_id');
                   
$articleOutgoing = DB::table('article_outgoing_document')
                   ->select('article_id', DB::raw('sum(quantity) as quantity'))
                   ->where('warehouse', $warehouse)
                   ->where('warehouse_type', '=', $warehouseType)
                   ->groupBy('article_id');
$articles = DB::table('articles as a')
        ->leftJoinSub($articleIncoming, 'i', function ($join) {
            $join->on('a.id', '=', 'i.article_id');
        })
        ->leftJoinSub($articleOutgoing, 'o', function ($join) {
            $join->on('a.id', '=', 'o.article_id');
        })
        //->where('i/o.quantity', '>', 0)  ?? specify quantity of incoming or outgoing, in case of both then add another where clause
        ->select([
            'a.id',
            'a.name',
            DB::raw('coalesce(i.quantity,0) -  coalesce(o.quantity,0) as quantity')
        ])
        //->having('quantity', '>', 0) ?? if you want to perform filter on subtraction of incoming and outgoing quantity then use having clause
        ->get();

Additionally I have used coalesce over result of sum(quantity) to ignore nulls like

coalesce(i.quantity,0) -  coalesce(o.quantity,0) as quantity

DB::raw('coalesce(i.quantity,0) -  coalesce(o.quantity,0) as quantity')

DEMO

Upvotes: 2

Related Questions