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. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
radhames brito
2010-Sep-10 11:51 UTC
Re: Cumulative Sum with will_paginate and activerecord
AR has a sum function. On Fri, Sep 10, 2010 at 4:45 AM, Fidel Viegas <fidel.viegas-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>wrote:> 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. > > -- > You received this message because you are subscribed to the Google Groups > "Ruby on Rails: Talk" group. > To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > For more options, visit this group at > http://groups.google.com/group/rubyonrails-talk?hl=en. > >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On 9/10/10, radhames brito <rbritom-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Frederick Cheung
2010-Sep-11 09:17 UTC
Re: Cumulative Sum with will_paginate and activerecord
On Sep 10, 7:50 pm, Fidel Viegas <fidel.vie...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 9/10/10, radhames brito <rbri...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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. >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> Best Regards, > > Fidel.-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Fidel Viegas
2010-Sep-11 09:45 UTC
Re: Re: Cumulative Sum with will_paginate and activerecord
On 9/11/10, Frederick Cheung <frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > > On Sep 10, 7:50 pm, Fidel Viegas <fidel.vie...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> On 9/10/10, radhames brito <rbri...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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. >> > > 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. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
radhames brito
2010-Sep-11 17:13 UTC
Re: Re: Cumulative Sum with will_paginate and activerecord
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]) On Sat, Sep 11, 2010 at 5:45 AM, Fidel Viegas <fidel.viegas-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>wrote:> On 9/11/10, Frederick Cheung <frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > > > On Sep 10, 7:50 pm, Fidel Viegas <fidel.vie...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > >> On 9/10/10, radhames brito <rbri...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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. > >> > > > > 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. > > -- > You received this message because you are subscribed to the Google Groups > "Ruby on Rails: Talk" group. > To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > For more options, visit this group at > http://groups.google.com/group/rubyonrails-talk?hl=en. > >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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(... On Sep 11, 1:13 pm, radhames brito <rbri...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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]) > > On Sat, Sep 11, 2010 at 5:45 AM, Fidel Viegas <fidel.vie...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>wrote: > > > On 9/11/10, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > On Sep 10, 7:50 pm, Fidel Viegas <fidel.vie...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > >> On 9/10/10, radhames brito <rbri...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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. > > > > 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. > > > -- > > You received this message because you are subscribed to the Google Groups > > "Ruby on Rails: Talk" group. > > To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > > To unsubscribe from this group, send email to > > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > > . > > For more options, visit this group at > >http://groups.google.com/group/rubyonrails-talk?hl=en.-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Fidel Viegas
2010-Sep-12 22:51 UTC
Re: Re: Cumulative Sum with will_paginate and activerecord
On 9/11/10, radhames brito <rbritom-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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]) >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. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Fidel Viegas
2010-Sep-12 22:53 UTC
Re: Re: Cumulative Sum with will_paginate and activerecord
On 9/11/10, Agoofin <thebserviss-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.