has_many :through and/or eager loading issues

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

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

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

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