Sum multiple rows and update by group_by a column. (Laravel elqoeunt)

Question

I want to SUM rows in MySQL table then merge and update. I'm trying to sum all the duplicate payments in a day. I will post my existing query but there are some limitations.

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"]);
}

Problem 1. I can only update one column at a time.

Problem 2. I'm programmatically looping through the rows and updating value, because of that if there is no duplicate value then the row not getting updated. I have to write another query to update. To delete existing query also.

Problem 3. It works well only for two payments.

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

Can you add some sample data before and after you run the queries? — Jonas Staudenmeir, Apr 28 '18 at 13:36
Thank you for suggesting me an edit. Please check the tables, now I hope the question is clear. — Fasil kk, Apr 28 '18 at 17:53
Should all of a day's payments get merged? Regardless of `merchant_id` and `investor_id`? — Jonas Staudenmeir, Apr 28 '18 at 18:15
Only if merchant_id and investor_id equal, example table updated. — Fasil kk, Apr 28 '18 at 18:48

Jonas Staudenmeir · Answer 1 · 2018-05-04T22:15:35.473

1

Try this:

$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);

edited May 04 '18 at 22:15

answered Apr 28 '18 at 22:18

Jonas Staudenmeir

24,815
6
63
109

yeah, Good solution, but update query inside the loop, – Fasil kk May 04 '18 at 20:35
How do you mean that? Do you want a single `UPDATE` query for all payments? – Jonas Staudenmeir May 04 '18 at 20:43
In the above solution, if there are 1000s of payments, then the update query will run 1000s time and destroy query will run. Maybe we can add all the $ids to an array and destroy altogether. Maybe a similar method for the update also? what you think about that. – Fasil kk May 04 '18 at 22:09
1

I improved the `DELETE` query in my answer. For `UPDATE` queries this is [a lot more complicated](https://stackoverflow.com/a/20255203/4848587) and probably not worth the effort. – Jonas Staudenmeir May 04 '18 at 22:18

Sum multiple rows and update by group_by a column. (Laravel elqoeunt)

1 Answers1