Cumulative Sum with will_paginate and activerecord

Rails
1

Hi everyone,

I am trying to a cumulative sum on each page using will_paginate. I
don’t want the sum of each page, but the cumulative sum. Here is an
example:

on Page 1 I have:

| date | in | out | balance |

|12-03-2010| 23.0 | 0.0 | 23.0 |

|12-03-2010| 23.0 | 0.0 | 46.0 |

|12-03-2010| 43.0 | 0.0 | 89.0 |

|12-03-2010| 53.0 | 0.0 | 142.0 |

|12-03-2010| 0.0 | 26.0 | 116.0 |

|12-03-2010| 25.0 | 0.0 | 141.0 |

|12-03-2010| 0.0 | 17.0 | 124.0 |

|12-03-2010| 0.0 | 25.0 | 99.0 |

|12-03-2010| 33.0 | 0.0 | 132.0 |

Total | 200.0 | 68.0 | 132.0 |

On page 2 I have:

| date | in | out | balance |

|12-03-2010| 23.0 | 0.0 | 155.0 |

|12-03-2010| 0.0 | 45.0 | 110.0 |

|12-03-2010| 43.0 | 0.0 | 153.0 |

|12-03-2010| 53.0 | 0.0 | 206.0 |

Total | 319.0 | 113.0 | 206.0 |

etc, etc,etc…

Has anyone done something similar to this before using ActiveRecord
and will_paginate?

I look forward to hearing from you some suggestions.

Regards,

Fidel.

2

AR has a sum function.

3

On 9/10/10, radhames brito [email protected] wrote:

AR has a sum function.

Hi Radhames,

I am aware of the sum function, but it does not do cumulative sum on
its own. I know how to do it using recursive queries or stored
procedures, but my question is how to deal with that using
will_paginate.

I will try to read the api and see if I can tweak it.

Best Regards,

Fidel.

4

On Sep 10, 7:50 pm, Fidel V. [email protected] wrote:

I will try to read the api and see if I can tweak it.

The sum function takes conditions just like find does. Depending on
what you are sorting on you should be able to construct the relevant
conditions from the items will paginate fetches for you.

Fred

5

On 9/11/10, Frederick C. [email protected] wrote:

its own. I know how to do it using recursive queries or stored
procedures, but my question is how to deal with that using
will_paginate.

I will try to read the api and see if I can tweak it.

The sum function takes conditions just like find does. Depending on
what you are sorting on you should be able to construct the relevant
conditions from the items will paginate fetches for you.

For the problem in question that does not work. I read through the
documentation and found out that I could paginate_by_sql, which is
what I really needed.

Thanks a lot!!!

Regards,

Fidel.

6

add a hidden field with [] in them it will make the params create an
array and put the ids of the paged record in them , then
pass the array in a find and call sum on it.

f.hidden :things, thing.id[]

then

find(params[:things])

7

Or include a separate variable with the sum you want to include in
addition to the will_paginate variable.

@figures = Figures.paginate…
@figure_sum = Figures.sum(…

8

On 9/11/10, radhames brito [email protected] wrote:

That is quite a lot of work. In sql I can do this “select date, in,
out, @total := @total + in - out as balance from mytable”. My problem
(or maybe I was being lazy) was more with will_paginate. After reading
the documentation I found the paginate_by_sql method, which was what I
was looking for.

The problem I am trying to solve cannot be resolved using
ActiveRecord’s default methods. I need to use sql, because it is not
possible to replicate that in plain AR, or maybe it is quite a lot of
work. This is one of those cases where you better use sql than AR
built-in methods. The sum method would never solve this problem alone,
because I need the cumulative sum on each row and in the last page the
running total as well.

so, using the paginate_by_sql is the cleanest solution to my problem.
I only have to pass the value of @total to each page as a hidden param
so that it can continue with the sum on the next page.

Best Regards,

Fidel.

9

On 9/11/10, Agoofin [email protected] wrote:

Or include a separate variable with the sum you want to include in
addition to the will_paginate variable.

@figures = Figures.paginate…
@figure_sum = Figures.sum(…

This will only give me the running total per page, not the cumulative
sum on a row by row basis. @figure_sum is similar to what I am using
to pass the running total up to the current page into the next page.

Thanks for the reply though.

Regards,

Fidel.