I have two tables one transactions
and another transaction_charges
each transaction might have multiple transaction charges means an has many relationships. now what I need is to get transactions that occured in a month/year/week. it would be better if I can do a group by
inside the query itself rather than doing a loop with the model collections.
so far I have written code to fetch the transactions with its total charge
$defaults = collect([]);
/** @var Builder $transactions */
$transactions = app(TransactionServices::class)
->getTransactions($defaults->merge($options))
->has('charges')->has('operation')->selectSub(function ($query){
/** @var Builder $query */
$query->selectRaw('SUM(amount)')->from('transaction_charges tc')
->whereRaw('tc.transaction_id = transaction.transaction_id');
}, 'totalCharge');
but to get the monthly result I need to do a group by but I don't think the group by will fetch correct aggregated result from the subquery as here what I need is the sum of the totalCharge
in a month/week/group. so how I can get the transactions with its charges sum up to the monthly/Yearly group