CODEWITHSUNDEEP
ruby-on-railsrubyrails-activerecord
0xSina
0xSina

Reputation: 21593

Why does ActiveRecord's :include do two queries?

I am just learning ActiveRecord and SQL and I was under the impression that :include does one SQL query. So if I do:

Show.first :include => :artist

It will execute one query and that query is going to return first show and artist. But looking at the SQL generated, I see two queries:

[2013-01-08T09:38:00.455705 #1179] DEBUG -- :   Show Load (0.5ms)  SELECT `shows`.* FROM `shows` LIMIT 1
[2013-01-08T09:38:00.467123 #1179] DEBUG -- :   Artist Load (0.5ms)  SELECT `artists`.* FROM `artists` WHERE `artists`.`id` IN (2)

I saw one of the Railscast videos where the author was going over :include vs :join and I saw the output SQL on the console and it was a large SQL query, but it was only one query. I am just wondering if this is how it is supposed to be or am I missing something?

Upvotes: 3

Views: 2608

Answers (2)

Frederick Cheung
Frederick Cheung

Reputation: 84182

Active Record has two ways in which it loads association up front. :includes will trigger either of those, based on some heuristics.

One way is for there to be one query per association: you first load all the shows (1 query) then you load all artists (2nd query). If you were then including an association on artists that would be a 3rd query. All of these queries are simple queries, although it does mean that no advantage is gained in your specific case. Because the queries are separate, you can't do things like order the top level (shows) by the child associations and thing like that.

The second way is to load everything in one big joins based query. This always produces a single query, but its more complicated - 1 join per association included and the code to turn the result set back into ruby objects is more complicated too. There are some other corner cases: polymorphic belongs_to can't be handled and including multiple has_many at the same level will produce a very large result set).

Active Record will by default use the first strategy (preload), unless it thinks that your query conditions or order are referencing the associations, in which case it falls back to the second approach. You can force the strategy used by using preload or eager_load instead of :includes.

Upvotes: 7

bullfrog
bullfrog

Reputation: 1633

Using :includes is a solution to provide eager loading. It will load at most two queries in your example. If you were to change your query Show.all :include => :artist. This will also call just two queries.

Better explanation: Active Record Querying Eager Loading

Upvotes: 1

Related Questions