CODEWITHSUNDEEP
phpmysqllaravel
Cristian25
Cristian25

Reputation: 109

laravel sum and group by in sub query

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?

Upvotes: 0

Views: 62

Answers (1)

Kim
Kim

Reputation: 79

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

Upvotes: 1

Related Questions