views:

499

answers:

2

Hello, I am new to rails and would appreciate some help optimizing my database usage.

Is there a way to load two models associated with each other with one DB query?

I have two models Person and Image:

class Person < ActiveRecord::Base
  has_many :images
end

class Image < ActiveRecord::Base
  belongs_to :person
end

I would like to load a set of people and their associated images with a single trip to the DB using a join command. For instance, in SQL, I can load all the data I need with the following query:

select * from people join images on people.id = images.person_id where people.id in (2, 3) order by timestamp;

So I was hoping that this rails snippet would do what I need:

>> people_and_images = Person.find(:all, :conditions => ["people.id in (?)", "2, 3"], :joins => :images, :order => :timestamp)

This code executes the SQL statement I am expecting and loads the instances of Person I need. However, I see that accessing a a Person's images leads to an additional SQL query.

>> people_and_images[0].images


Image Load (0.004889)   SELECT * FROM `images` WHERE (`images`.person_id = 2)

Using the :include option in the call to find() does load both models, however it will cost me an additional SELECT by executing it along with the JOIN.

I would like to do in Rails what I can do in SQL which is to grab all the data I need with one query.

Any help would be greatly appreciated. Thanks!

+2  A: 

You want to use :include like

Person.find(:all, :conditions => ["people.id in (?)", "2, 3"], :include => :images, :order => :timestamp)

Check out the find documentation for more details

Rob Di Marco
Hi Rob, I may not understand :include very well, but as noted in the post, I see via Query Analyzer that :include still causes an additional SELECT to be executed. So in raw SQL I execute one query. In Rails, it appears I need two.
peter
No, the include should do it in one query. Probably this is a better link to explain how that is done.http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.htmlAre you sure that it is the second query isn't coming from something else?
Rob Di Marco
Perhaps I am being mislead by my logs. I am using the query analyzer plugin (http://agilewebdevelopment.com/plugins/query_analyzer) to see the what sql statements are executed on my database. This call:people_and_images = Person.find(:all, :conditions => ["people.id in (?)", "2, 3"], :joins => :images, :order => :timestamp, :include => :images)Leads to two select statements being executed (see next comment):
peter
Person Load (0.005105) SELECT `people`.* FROM `people` INNER JOIN `images` ON images.person_id = people.id WHERE (people.id in ('2, 3')) ORDER BY timestampImage Load (0.002779) SELECT `images`.* FROM `images` WHERE (`images`.person_id IN (2))
peter
A: 

Hi Peter,

You can use :include for eager loading of associations and indeed it does call exactly 2 queries instead of one as with the case of :joins; the first query is to load the primary model and the second is to load the associated models. This is especially helpful in solving the infamous N+1 query problem, which you will face if you doesn't use :include, and :joins doesn't eager-load the associations.

the difference between using :joins and :include is 1 query more for :include, but the difference of not using :include will be a whole lot more.

you can check it up here: http://guides.rubyonrails.org/active_record_querying.html#eager-loading-associations

Staelen
Hi Staelen,I think my post is confusing people. My question really is: if I can grab all the data I need with one SQL join query, why can't I do the same in Rails? I am not really asking how to populate the 2 models. Instead, I'm asking why can't I populate the 2 models with the one call that is giving me all the data I need?In the example above, the find() command with the :joins option translates to a sql join query that actually fetches all the data needed to populate both Person and Image models. However, rails ends up only populating the Person model, which is inefficient.
peter
i think this blog sort of explain a whole lot more and give you a better idea what's going on: http://akitaonrails.com/2008/5/26/rolling-with-rails-2-1-the-first-full-tutorial-part-2hope it helps =)
Staelen
check out Optimized Eager Loading section =)
Staelen