如何从laravel中的两个不同的连接中减去两个列的和？

Question

我有一个articles表，我想连接两个不同的表，在这两个表中我跟踪收入和结果文章。我想用一张表中的数量之和与另一张表中的数量之和相减。这就是我所尝试的，但我总是得到数量为空的结果。

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

Answer 1

在查询中使用多个连接将导致多个行，例如，如果一个项目有来自传入表的2行，来自传出表的2行或更多相关行，这将产生不正确的sum值。要处理这种类型的问题，您可以为相关的表(传入和传出)使用子句，并在各自的子原因中计算每个文章的总和。在此之后，将这些子句与您的文章表连接起来，并减去它们的数量。

在普通SQL中，它将如下所示

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

在较新版本的laravel中，可以使用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();

此外，我在sum(quantity)的结果上使用了coalesce来忽略空值，比如

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

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

DEMO