0

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?

Fasil kk
  • 2,147
  • 17
  • 26

1 Answers1

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