douchui4459 2018-03-12 19:30
浏览 917

laravel eloquent的分组查询结果总和

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

  • 写回答

1条回答 默认 最新

  • dongzhi4073 2018-03-12 20:12
    关注

    I don't know well how your system works but I did an intent of understand it and think one possible solution for your problem.
    I made my best effort to explain the code. I Hope you understand that english is not my native language.

    // I suppose that TransactionServices represents "transaction" Table and has transaction_charges
    $transactions = TransactionServices::has('charges')->get(); // If I understand well your code that returns the transactions that has charges.
    // $transactions = TransactionServices::has('charges')->has('operation')->get(); // that returns the transactions that has charges AND operation.
    
    // the month to filter the charges
    $month = 'march'; // selected month can be dynamic this is an example
    
    // Get all TransactionServices with total amount of charges
    $transactionsWithTotalCharge = $transactions->each(function ($transaction, $key) {
        $totalAmount = $transaction->charges->where('month', $month)->sum('amount'); // This make the sum of all charges of march for that transaction 
        $transaction->put('chargesTotalAmount' , $totalAmount); // This add chargesTotalAmount to every TransactionServices
        return $transaction;
    });
    
    // You can verify if the sum is correct
    foreach($transactionsWithTotalCharge as $transaction) {
        // dd($transaction); 
        dd($transaction->chargesTotalAmount); 
    }
    

    I could not test that and hope that work

    评论

报告相同问题?

悬赏问题

  • ¥20 怎么在stm32门禁成品上增加记录功能
  • ¥15 Source insight编写代码后使用CCS5.2版本import之后,代码跳到注释行里面
  • ¥50 NT4.0系统 STOP:0X0000007B
  • ¥15 想问一下stata17中这段代码哪里有问题呀
  • ¥15 flink cdc无法实时同步mysql数据
  • ¥100 有人会搭建GPT-J-6B框架吗?有偿
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 解riccati方程组