Help with :include on active record finds

Rails
1

Hi All,
I am having trouble getting the :include option to work on my active
record
finds. They are not eager loading, adding the joins to the sql queries.
Has
anyone had this problem? Here is my query:

@cart = Cart.find_by_id(session[:cart_id], :include =>

[:cart_items])

This is generating the following query: SELECT * FROM carts WHERE
(carts.id = 23) LIMIT 1

Rails is not eager loading the cart_items which belongs_to cart. Does
anyone
know why?

Thanks,
Sean

2

Rails is not eager loading the cart_items which belongs_to cart. Does anyone
know why?

Don’t think it can. Not in a single query. You have one cart that has
many items. You can’t return all of that in a single query unless you
want to send duplicate rows for the cart for every row of the cart item.

There may be right after that line in the logs a line fetching the cart
items. Or Rails might not do it since it’s not possible.

If I’m thinking clearly that is :slight_smile:

3

Nah, that should work I think–see the “Eager Loading” section on
http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethod
s.html

I just tried this out in a toy app of my own, and was surprised to see
in my log that in addition to the left-join query immediately under
the heading “Processing OrganizationsController#show”, there were 3
other queries that followed the heading “Rendering organizations/show”.
One was triggered by (I think) this line in my view:

<% if @organization.people.count > 0 %>

That generated a:

SELECT count(*) AS count_all FROM people WHERE
(people.organization_id = 1)

Then I have 2 instances of:

SELECT * FROM organizations WHERE (organizations.id = 1)

Which I believe are caused by resolving the person.organization.name
calls in the part of the view that lists the people belonging to
@organization.

So… not that this isn’t a bit dismaying, BUT–is there any chance that
you too are looking at calls generated not by your find_by_id call, but
by subsequent view activity?

Cheers,

-Roy

4

On Jun 6, 10:15 pm, “Sean O’Hara” [email protected] wrote:

Hi All,
I am having trouble getting the :include option to work on my active record
finds. They are not eager loading, adding the joins to the sql queries. Has
anyone had this problem? Here is my query:

Eager loading in rails 2.1 changed:

Fred

5

I don’t think it has to do with view activity, because the cart and
cart_items queries are generated by a before_filter… and the are only
run
once. Plus, they are instantiating the @cart and it’s .cart_items
children,
so I don’t think any view methods would affect that. Maybe in your case
you
can call @organization.people.size to avoid the sql. There are other
finds
in my site that aren’t generating the eager loading queries like I would
expect (and I believe rails used to, prior to rails 2.0.2).
Here’s the kind of query I would expect rails to generated based on the
find
statement:
mysql> select * from carts LEFT OUTER JOIN cart_items ON carts.id =
cart_items.cart_id WHERE carts.id = 23;
±—±--------------------±--------------------±-----±--------±-----------±----------±---------+
| id | created_at | updated_at | id | cart_id |
product_id | option_id | quantity |
±—±--------------------±--------------------±-----±--------±-----------±----------±---------+
| 23 | 2008-06-06 16:13:17 | 2008-06-06 16:13:17 | 60 | 23 |
929 | NULL | 1 |
| 23 | 2008-06-06 16:13:17 | 2008-06-06 16:13:17 | 61 | 23 |
933 | NULL | 1 |
| 23 | 2008-06-06 16:13:17 | 2008-06-06 16:13:17 | 62 | 23 |
938 | NULL | 1 |
±—±--------------------±--------------------±-----±--------±-----------±----------±---------+
3 rows in set (0.01 sec)

I’m pretty sure rails used to do this. I find it strange that it no
longer
does. Thanks for the suggestions.

6

Ah–you’re right about changing .count to .size. That does dissappear
the count(*) query. Thanks!

Like I say–I do get the LEFT JOIN query. It’s not as pretty as yours,
but it’s there:

SELECT organizations.id AS t0_r0,
organizations.name AS t0_r1,
organizations.abbreviation AS t0_r2,
organizations.city AS t0_r3,
organizations.state AS t0_r4,
organizations.crn_site AS t0_r5,
organizations.created_at AS t0_r6,
organizations.updated_at AS t0_r7,
people.id AS t1_r0,
people.first_name AS t1_r1,
people.last_name AS t1_r2,
people.birth_date AS t1_r3,
people.organization_id AS t1_r4,
people.created_at AS t1_r5,
people.updated_at AS t1_r6
FROM organizations LEFT OUTER JOIN
people
ON people.organization_id = organizations.id WHERE
(organizations.id = ‘1’)

That’s with rails 2.0.2 and mysql. What’s your version & db?

(There’s no chance that you left off the ‘has_many :cart_items’
declaration in your cart class is there?)

From: [email protected]
[mailto:[email protected]] On Behalf Of Sean O’Hara
Sent: Friday, June 06, 2008 3:27 PM
To: [email protected]
Subject: [Rails] Re: help with :include on active record finds

I don’t think it has to do with view activity, because the cart and
cart_items queries are generated by a before_filter… and the are only
run once. Plus, they are instantiating the @cart and it’s .cart_items
children, so I don’t think any view methods would affect that. Maybe in
your case you can call @organization.people.size to avoid the sql. There
are other finds in my site that aren’t generating the eager loading
queries like I would expect (and I believe rails used to, prior to rails
2.0.2).

Here’s the kind of query I would expect rails to generated based on the
find statement:
mysql> select * from carts LEFT OUTER JOIN cart_items ON carts.id =
cart_items.cart_id WHERE carts.id = 23;
±—±--------------------±--------------------±-----±--------±----
-------±----------±---------+
| id | created_at | updated_at | id | cart_id |
product_id | option_id | quantity |
±—±--------------------±--------------------±-----±--------±----
-------±----------±---------+
| 23 | 2008-06-06 16:13:17 | 2008-06-06 16:13:17 | 60 | 23 |
929 | NULL | 1 |
| 23 | 2008-06-06 16:13:17 | 2008-06-06 16:13:17 | 61 | 23 |
933 | NULL | 1 |
| 23 | 2008-06-06 16:13:17 | 2008-06-06 16:13:17 | 62 | 23 |
938 | NULL | 1 |
±—±--------------------±--------------------±-----±--------±----
-------±----------±---------+
3 rows in set (0.01 sec)

I’m pretty sure rails used to do this. I find it strange that it no
longer does. Thanks for the suggestions.

On Fri, Jun 6, 2008 at 6:16 PM, Pardee, Roy [email protected] wrote:

Nah, that should work I think–see the “Eager Loading” section
on

http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethod
s.html
<http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMetho
ds.html>

I just tried this out in a toy app of my own, and was surprised
to see
in my log that in addition to the left-join query immediately
under
the heading “Processing OrganizationsController#show”, there
were 3
other queries that followed the heading “Rendering
organizations/show”.
One was triggered by (I think) this line in my view:

<% if @organization.people.count > 0 %>

That generated a:

SELECT count(*) AS count_all FROM people WHERE
(people.organization_id = 1)

Then I have 2 instances of:

SELECT * FROM organizations WHERE (organizations.id = 1)

Which I believe are caused by resolving the
person.organization.name
calls in the part of the view that lists the people belonging to
@organization.

So… not that this isn’t a bit dismaying, BUT–is there any
chance that
you too are looking at calls generated not by your find_by_id
call, but
by subsequent view activity?

Cheers,

-Roy

-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of Philip
Hallstrom
Sent: Friday, June 06, 2008 2:20 PM
To: [email protected]
Subject: [Rails] Re: help with :include on active record finds

Hi All,
I am having trouble getting the :include option to work on my
active
record finds. They are not eager loading, adding the joins to
the sql
queries. Has anyone had this problem? Here is my query:

@cart = Cart.find_by_id(session[:cart_id], :include =>
[:cart_items])

This is generating the following query: SELECT * FROM carts
WHERE
(carts.id = 23) LIMIT 1

Rails is not eager loading the cart_items which belongs_to
cart. Does
anyone know why?

Don’t think it can. Not in a single query. You have one cart
that has
many items. You can’t return all of that in a single query
unless you
want to send duplicate rows for the cart for every row of the
cart item.

There may be right after that line in the logs a line fetching
the cart
items. Or Rails might not do it since it’s not possible.

If I’m thinking clearly that is :slight_smile:

7

On 6 Jun 2008, at 22:20, Philip H. wrote:

want to send duplicate rows for the cart for every row of the cart
item.

That’s what Rails used to do :slight_smile:

Fred