Question 17064 in Laravel Question posted in
The official Laravel Documentation can be found here.

MySql Statement History with user balance algorithm – Laravel

I have a table with payment history

payments:

id consumer_id amount created_at
1 1 30 2021-05-11 13:01:36
2 1 -10 2021-05-12 14:01:36
3 1 -2.50 2021-05-13 13:01:36
4 1 -4.50 2021-05-14 13:01:36
5 1 20 2021-05-15 13:01:36

In final result need to get consumer balance after each transaction.
So something like this

id consumer_id amount created_at balance
1 1 30 2021-05-11 13:01:36 30.00
2 1 -10 2021-05-12 14:01:36 20.00
3 1 -2.50 2021-05-13 13:01:36 17.50
4 1 -4.50 2021-05-14 13:01:36 13.00
5 1 20 2021-05-15 13:01:36 33.00

I using this query

SET @balanceTotal = 0;
select amount, created_at, consumer_id, @balanceTotal := @balanceTotal + amount as balance
from payments
where consumer_id = 1

This works fine until I try to add some sorting or pagination.

Any suggestion on how to write a query with order by desc, limit, and offset to count balance properly?

Tags:

2

Answers


  1. 0

    That’s just a window sum. In MySQL 8.0:

    select p.*, 
    sum(amount) over(partition by consumer_id order by created_at) balance
from payments p

    You can add the filtering on the customer in the where clause if you like (in which case the partition by clause is not really needed anymore).

    In earlier versions of MySQL, an alternative uses a correlated subquery:

    select p.*, 
    (
        select sum(amount) 
        from payments p1 
        where p1.consumer_id = p.consumer_id and p1.created_at <= p.created_at
    ) balance
from payments p

    I would not recommend user variables for this; although efficient, their behavior is quite tricky, and their use is deprecated in recent. versions.

    Login or Signup to reply.
  2. 0

    If using MySQL >= 8 using a window sum is preferable –

    select p.*, sum(amount) over(order by created_at) balance
from payments p
where consumer_id = 1
order by created_at desc
limit 0, 5;

    If you are using MySQL < 8 then using a user variable for this is significantly more efficient than using the suggested correlated subquery. You can have it as a derived table for re-ordering and pagination –

    select * from (
    select p.*, @balanceTotal := @balanceTotal + amount as balance
    from payments p, (SELECT @balanceTotal := 0) vars
    where consumer_id = 1
    order by created_at
) t
order by created_at desc
limit 0, 5;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search