首页 文章

对多行求和并按group_by列进行更新 . （Laravel elqoeunt）

提问于
浏览 次
0

我想在MySQL表中SUM行然后合并和更新 . 我想在一天内汇总所有重复付款 . 我将发布我现有的查询，但有一些限制 .

Example:

+----+------------+-------------+-------------+---------+
| id |    date    | merchant_id | investor_id | payment |
+----+------------+-------------+-------------+---------+
|  1 | 27/05/1989 |           4 |           7 |    1000 |
|  2 | 27/05/1989 |           4 |           7 |    1200 |
|  3 | 28/05/1989 |           4 |           7 |    1500 |
|  4 | 28/05/1989 |           4 |           7 |    1000 |
|  5 | 28/05/1989 |           5 |           8 |    1000 |
+----+------------+-------------+-------------+---------+

Expected result:

+----+------------+-------------+-------------+---------+
| id |    date    | merchant_id | investor_id | payment |
+----+------------+-------------+-------------+---------+
|  1 | 27/05/1989 |           4 |           7 |    2200 |
|  3 | 28/05/1989 |           4 |           7 |    2500 |
|  5 | 28/05/1989 |           5 |           8 |    1000 |
+----+------------+-------------+-------------+---------+

I tried this loop.

foreach ($existing_payments as $key => $payment) 
{
    ParticipentPayment::where('payment_date',$payment->payment_date) ->update(['payment' => \DB::raw("payment+$payment->payment"]);
}

问题1.我一次只能更新一列 .

问题2.我以编程方式循环遍历行并更新值，因为如果没有重复值，则行不会更新 . 我必须写另一个查询来更新 . 还要删除现有查询 .

问题3.它仅适用于两次付款 .

Is there any possible solution in laravel eloquent merge(with sum) multiple rows and update?

php mysql laravel eloquent

1 回答

  • 1

    试试这个：

    $delete = [];
$payments = ParticipentPayment::selectRaw("GROUP_CONCAT(`id` SEPARATOR ',') `ids`")
    ->selectRaw('SUM(`payment`) `sum`')
    ->groupBy('date', 'merchant_id', 'investor_id')
    ->get();
foreach($payments as $payment)
{
    $ids = explode(',', $payment->ids);
    if(count($ids) == 1) continue;
    ParticipentPayment::whereKey($ids[0])->update(['payment' => $payment->sum]);
    $delete = array_merge($delete, array_slice($ids, 1));
}
ParticipentPayment::destroy($delete);
    回复于

相关问题