# Has\_many :through and/or eager loading issues

**URL:** <https://www.ruby-forum.com/t/has-many-through-and-or-eager-loading-issues/140191>\
**Category:** Rails\
**Created:** [June 11, 2008, 7:26pm UTC](https://www.ruby-forum.com/t/has-many-through-and-or-eager-loading-issues/140191 "2008-06-11T19:26:03Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![sabon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/7feea3/32.png) [@sabon](https://www.ruby-forum.com/u/sabon)\
**Post date:** [June 11, 2008, 7:26pm UTC](https://www.ruby-forum.com/t/has-many-through-and-or-eager-loading-issues/140191/1 "2008-06-11T19:26:03Z")

</div>

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:  
**Expenses**  
  
\<% for category in @recent\_expenses %\>  
\<%= category.name %\>  
\<%= category.amounts.sum(:value) %\>  
  
\<% 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

---

<div class="post-metadata">

**Author:** ![sabon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/7feea3/32.png) [@sabon](https://www.ruby-forum.com/u/sabon)\
**Post date:** [June 11, 2008, 7:49pm UTC](https://www.ruby-forum.com/t/has-many-through-and-or-eager-loading-issues/140191/2 "2008-06-11T19:49:11Z")

</div>

On 11 Jun 2008, at 18:26, Sabon [s] wrote:

> 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:  
> **Expenses**  
>   
> \<% for category in @recent\_expenses %\>  
> \<%= category.name %\>  
> \<%= category.amounts.sum(:value) %\>  
>   
> \<% 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

---

<div class="post-metadata">

**Author:** ![sabon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/7feea3/32.png) [@sabon](https://www.ruby-forum.com/u/sabon)\
**Post date:** [June 12, 2008, 2:38am UTC](https://www.ruby-forum.com/t/has-many-through-and-or-eager-loading-issues/140191/3 "2008-06-12T02:38:28Z")

</div>

> 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

---

<div class="post-metadata">

**Author:** ![sabon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/7feea3/32.png) [@sabon](https://www.ruby-forum.com/u/sabon)\
**Post date:** [June 12, 2008, 9:48am UTC](https://www.ruby-forum.com/t/has-many-through-and-or-eager-loading-issues/140191/4 "2008-06-12T09:48:13Z")

</div>

On Jun 12, 1:31 am, “[s]” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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