I''m stuck with some issues I can''t overcome by myself, please help. What I want to achieve: I want to get the list of spend per category for the current user for the last 30 days. Preferably also sorted by spend, descending (to present it nicely in a graph). Models: class Category < ActiveRecord::Base belongs_to :user has_many :amounts has_many :operations, :through => :amounts acts_as_tree :order => "name" end class Operation < ActiveRecord::Base belongs_to :user has_many :amounts has_one :payee has_many :categories, :through => :amounts end class Amount < ActiveRecord::Base belongs_to :operation belongs_to :category end Amounts table structure: id:integer category_id:integer operation_id:integer value:decimal(9,2) What I try to do: @recent_expenses = current_user.categories.find(:all, :include => [:amounts, :operations], :conditions => [''operations.date > ? AND cat_type = "Expense" AND amounts.value <> 0'', (Time.now - 30.days).to_date]) First of all, it fails. I don''t know why but it takes ALL the expenses instead of just those from the last 30 days. Second, it doesn''t obviously neither sum those expenses nor sort it by value descending. I then do in the view: <b>Expenses</b><br /> <% for category in @recent_expenses %> <%= category.name %> <%= category.amounts.sum(:value) %><br /> <% end %> It gives me those summed values but it also fires additional SQL queries for each category to sum it up. And, as mentioned above, it sums all the entries, not just from the last month. I know I''m doing something terribly wrong here. Are my has_many :through dependencies wrong? Is my eager loading handled the wrong way? Am I missing something else? Any help would be much appreciated. Radek -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 11 Jun 2008, at 18:26, Sabon [s] wrote:> > I''m stuck with some issues I can''t overcome by myself, please help. > > > What I try to do: > @recent_expenses = current_user.categories.find(:all, :include => > [:amounts, :operations], :conditions => [''operations.date > ? AND > cat_type = "Expense" AND amounts.value <> 0'', (Time.now - > 30.days).to_date]) > > First of all, it fails. > I don''t know why but it takes ALL the expenses instead of just those > from the last 30 days.have you tried with just :include => :operations ? Rails knows it needs to join accounts for this, joining it twice might well screw things up.> > Second, it doesn''t obviously neither sum those expenses nor sort it by > value descending. > I then do in the view: > <b>Expenses</b><br /> > <% for category in @recent_expenses %> > <%= category.name %> > <%= category.amounts.sum(:value) %><br /> > <% end %>Sum never uses loaded associations. if you want it to you''ll need to do it yourself (ie category.amounts.to_a.sum {|amount| amount.value}). The to_a is needed to that you get Array#sum rather than the ActiveRecord sum Fred> > > It gives me those summed values but it also fires additional SQL > queries > for each category to sum it up. And, as mentioned above, it sums all > the > entries, not just from the last month. > > I know I''m doing something terribly wrong here. > Are my has_many :through dependencies wrong? > Is my eager loading handled the wrong way? > Am I missing something else? > > Any help would be much appreciated. > > Radek > -- > Posted via http://www.ruby-forum.com/. > > >--~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> > What I try to do: > > @recent_expenses = current_user.categories.find(:all, :include => > > [:amounts, :operations], :conditions => [''operations.date > ? AND > > cat_type = "Expense" AND amounts.value <> 0'', (Time.now - > > 30.days).to_date]) > > > > First of all, it fails. > > I don''t know why but it takes ALL the expenses instead of just those > > from the last 30 days. > > have you tried with just :include => :operations ? Rails knows it > needs to join accounts for this, joining it twice might well screw > things up.I tried to figure things out from the end. This is the SQL query that gives me exactly what I want: category type, category name and the total for that category from the last 30 days, ordered by total spend per category. How do you wrap it with ActiveRecord? Or am I stuck with pure SQL here? SELECT `categories`.`cat_type` AS TYPE , `categories`.`name` AS NAME, SUM( `amounts`.`value` ) AS TOTAL FROM `categories` LEFT OUTER JOIN `amounts` ON ( `categories`.`id` = `amounts`.`category_id` ) LEFT OUTER JOIN `operations` ON ( `operations`.`id` = `amounts`. `operation_id` ) WHERE ( categories.user_id =1 AND ( operations.date > ''2008-05-13'' AND cat_type = "Expense" AND amounts.value <>0 ) ) GROUP BY NAME ORDER BY TOTAL ASC --~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Jun 12, 1:31 am, "[s]" <rade...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > I tried to figure things out from the end. > This is the SQL query that gives me exactly what I want: category type, > category name and the total for that category from the last 30 days, ordered > by total spend per category. > How do you wrap it with ActiveRecord? > Or am I stuck with pure SQL here?Pure sql i''m afraid. bang it in find_by_sql. Fred> > SELECT `categories`.`cat_type` AS > TYPE , `categories`.`name` AS NAME, SUM( `amounts`.`value` ) AS TOTAL > FROM `categories` > LEFT OUTER JOIN `amounts` ON ( `categories`.`id` = `amounts`.`category_id` ) > LEFT OUTER JOIN `operations` ON ( `operations`.`id` = `amounts`. > `operation_id` ) > WHERE ( categories.user_id =1 > AND ( operations.date > ''2008-05-13'' > AND cat_type = "Expense" > AND amounts.value <>0 > ) > ) > GROUP BY NAME > ORDER BY TOTAL ASC--~--~---------~--~----~------------~-------~--~----~ 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-/JYPxA39Uh5TLH3MbocFFw@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 -~----------~----~----~----~------~----~------~--~---