laravel sum and group by in sub query

Question

I have this query

$t_items_between = TransferItem::whereHas('transfer', function ($query) use($old_until_date, $inventory_id, $old_from_date_interval) {
                $query->where('document_date', '>=', $old_from_date_interval);
                $query->where('document_date', '<=', $old_until_date);
                $query->where('to_inventory_id', $inventory_id);
            })
            ->with(['transfer' => function($query) use($old_until_date, $inventory_id, $old_from_date_interval) {
                $query->where('document_date', '>=', $old_from_date_interval);
                $query->where('document_date', '<=', $old_until_date);
                $query->where('to_inventory_id', $inventory_id);
            }
            ])
            ->whereIn('item_id', $subset)
            ->addSelect(['quantity' => TransferItem::selectRaw('sum(quantity)')
                ->groupBy('item_stock_id')
                ->from((new TransferItem)->getTable() . ' as ti')
                ->whereColumn('ti.item_stock_id', (new TransferItem)->getTable() . '.item_stock_id')
        ])
            ->get();

So TransferItem is the model of the table transfer_items. transfer_items has 4 columns: id, transfer_id, item_stock_id and quantity.

What I am trying to do is to sum the quantity of each result, grouping by the item_stock_id, but the query above doesn't work. How would I approach it?

Kim · Accepted Answer · 2022-08-12 12:23:37Z

1

try using map

$t_items_between = TransferItem::whereHas('transfer', function ($query) use($old_until_date, $inventory_id, $old_from_date_interval) {
$query->where('document_date', '>=', $old_from_date_interval);
$query->where('document_date', '<=', $old_until_date); $query->where('to_inventory_id', $inventory_id);
    })
    ->with(['transfer' => function($query) use($old_until_date, $inventory_id, $old_from_date_interval) {
    $query->where('document_date', '>=', $old_from_date_interval);
    $query->where('document_date', '<=', $old_until_date); $query->where('to_inventory_id', $inventory_id);
        }
        ])
        ->whereIn('item_id', $subset)
        ->get()
        ->groupBy('item_stock_id')
        ->map(function ($q) {
        return $q->sum('quantity');
        });

Visit https://laravel.com/docs/9.x/collections

answered Aug 12, 2022 at 12:23

Kim

791 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

laravel sum and group by in sub query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related