Ruby-on-rails – Rails :include vs. :joins

databasejoin;rails-activerecordrubyruby-on-rails

This is more of a "why do things work this way" question rather than a "I don't know how to do this" question…

So the gospel on pulling associated records that you know you're going to use is to use :include because you'll get a join and avoid a whole bunch of extra queries:

Post.all(:include => :comments)

However when you look at the logs, there's no join happening:

Post Load (3.7ms)   SELECT * FROM "posts"
Comment Load (0.2ms)   SELECT "comments.*" FROM "comments" 
                       WHERE ("comments".post_id IN (1,2,3,4)) 
                       ORDER BY created_at asc)

It is taking a shortcut because it pulls all of the comments at once, but it's still not a join (which is what all the documentation seems to say). The only way I can get a join is to use :joins instead of :include:

Post.all(:joins => :comments)

And the logs show:

Post Load (6.0ms)  SELECT "posts".* FROM "posts" 
                   INNER JOIN "comments" ON "posts".id = "comments".post_id

Am I missing something? I have an app with half a dozen associations and on one screen I display data from all of them. Seems like it would be better to have one join-ed query instead of 6 individuals. I know that performance-wise it's not always better to do a join rather than individual queries (in fact if you're going by time spent, it looks like the two individual queries above are faster than the join), but after all the docs I've been reading I'm surprised to see :include not working as advertised.

Maybe Rails is cognizant of the performance issue and doesn't join except in certain cases?

Best Answer

It appears that the :include functionality was changed with Rails 2.1. Rails used to do the join in all cases, but for performance reasons it was changed to use multiple queries in some circumstances. This blog post by Fabio Akita has some good information on the change (see the section entitled "Optimized Eager Loading").

Related Solutions

Ruby-on-rails – Rails: How to set default values in ActiveRecord

There are several issues with each of the available methods, but I believe that defining an after_initialize callback is the way to go for the following reasons:

default_scope will initialize values for new models, but then that will become the scope on which you find the model. If you just want to initialize some numbers to 0 then this is not what you want.
Defining defaults in your migration also works part of the time... As has already been mentioned this will not work when you just call Model.new.
Overriding initialize can work, but don't forget to call super!
Using a plugin like phusion's is getting a bit ridiculous. This is ruby, do we really need a plugin just to initialize some default values?
Overriding after_initialize is deprecated as of Rails 3. When I override after_initialize in rails 3.0.3 I get the following warning in the console:

DEPRECATION WARNING: Base#after_initialize has been deprecated, please use Base.after_initialize :method instead. (called from /Users/me/myapp/app/models/my_model:15)

Therefore I'd say write an after_initialize callback, which lets you default attributes in addition to letting you set defaults on associations like so:

  class Person < ActiveRecord::Base
    has_one :address
    after_initialize :init

    def init
      self.number  ||= 0.0           #will set the default value only if it's nil
      self.address ||= build_address #let's you set a default association
    end
  end

Now you have just one place to look for initialization of your models. I'm using this method until someone comes up with a better one.

Caveats:

For boolean fields do:

self.bool_field = true if self.bool_field.nil?

See Paul Russell's comment on this answer for more details
If you're only selecting a subset of columns for a model (ie; using select in a query like Person.select(:firstname, :lastname).all) you will get a MissingAttributeError if your init method accesses a column that hasn't been included in the select clause. You can guard against this case like so:

self.number ||= 0.0 if self.has_attribute? :number

and for a boolean column...

self.bool_field = true if (self.has_attribute? :bool_value) && self.bool_field.nil?

Also note that the syntax is different prior to Rails 3.2 (see Cliff Darling's comment below)

Sql – the difference between Left, Right, Outer and Inner Joins?

Simple Example: Let's say you have a Students table, and a Lockers table. In SQL, the first table you specify in a join, Students, is the LEFT table, and the second one, Lockers, is the RIGHT table.

Each student can be assigned to a locker, so there is a LockerNumber column in the Student table. More than one student could potentially be in a single locker, but especially at the beginning of the school year, you may have some incoming students without lockers and some lockers that have no students assigned.

For the sake of this example, let's say you have 100 students, 70 of which have lockers. You have a total of 50 lockers, 40 of which have at least 1 student and 10 lockers have no student.

INNER JOIN is equivalent to "show me all students with lockers".
Any students without lockers, or any lockers without students are missing.
Returns 70 rows

LEFT OUTER JOIN would be "show me all students, with their corresponding locker if they have one".
This might be a general student list, or could be used to identify students with no locker.
Returns 100 rows

RIGHT OUTER JOIN would be "show me all lockers, and the students assigned to them if there are any".
This could be used to identify lockers that have no students assigned, or lockers that have too many students.
Returns 80 rows (list of 70 students in the 40 lockers, plus the 10 lockers with no student)

FULL OUTER JOIN would be silly and probably not much use.
Something like "show me all students and all lockers, and match them up where you can"
Returns 110 rows (all 100 students, including those without lockers. Plus the 10 lockers with no student)

CROSS JOIN is also fairly silly in this scenario.
It doesn't use the linked lockernumber field in the students table, so you basically end up with a big giant list of every possible student-to-locker pairing, whether or not it actually exists.
Returns 5000 rows (100 students x 50 lockers). Could be useful (with filtering) as a starting point to match up the new students with the empty lockers.

Best Answer

Related Solutions

Ruby-on-rails – Rails: How to set default values in ActiveRecord

Sql – the difference between Left, Right, Outer and Inner Joins?

Related Topic