Блог

У меня есть articles таблица, и я хочу объединить две разные таблицы, в которых я отслеживаю статьи о доходах и результатах. Я хочу вычесть сумму величин из одной таблицы с суммой величин из другой таблицы. Это то, что я пытаюсь, но я всегда получаю null в количестве.

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

Использование нескольких объединений в запросе приведет к появлению нескольких строк, например, если одна статья содержит 2 строки из входящей таблицы, 2 или более связанных строк из исходящей таблицы, что приведет к неправильному значению суммы. Для решения такого типа проблемы вы можете использовать подпункты для связанных таблиц (входящих и исходящих) и вычислять сумму для каждой статьи в их соответствующей подпричине. После этого соедините эти подпункты с вашей таблицей статей и вычтите их количество.

В обычном 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();
  

Кроме того, я использовал coalesce над результатом sum(quantity) , чтобы игнорировать нули, такие как

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

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

ДЕМОНСТРАЦИЯ