How to use includes with 3 models w ActiveRecord?

Given the following model:

Room (id, title, suggested)
  has_many :room_apps, :dependent => :destroy

RoomApp (room_id, app_id, appable_id, appable_type)
  belongs_to :appable, :polymorphic => true
  has_many :colors, :as => :appable
  has_many :shirts, :as => :appable

Colors (room_id)
  belongs_to :room
  belongs_to :room_app
  belongs_to :app

What I want to do is get all the suggested rooms. In my controller I have:

@suggested_rooms = Room.includes(:room_apps).find_all_by_suggested(true).first(5)

Problem here is the includes is not working and the db is being hit several times:

Processing by PagesController#splash as HTML
  Room Load (0.6ms)  SELECT "rooms".* FROM "rooms" WHERE "rooms"."suggested" = 't' ORDER BY last_activity_at DESC
  RoomApp Load (0.6ms)  SELECT "room_apps".* FROM "room_apps" WHERE "room_apps"."published" = 't' AND ("room_apps".room_id IN (5,4,3)) ORDER BY created_at DESC
  RoomApp Load (5.9ms)  SELECT "room_apps".* FROM "room_apps" WHERE "room_apps"."published" = 't' AND "room_apps"."id" = 6 AND ("room_apps".room_id = 5) ORDER BY created_at DESC LIMIT 1
  Color Load (0.4ms)  SELECT "colors".* FROM "colors" WHERE "colors"."id" = 5 LIMIT 1
  RoomApp Load (0.6ms)  SELECT "room_apps".* FROM "room_apps" WHERE "room_apps"."published" = 't' AND "room_apps"."id" = 5 AND ("room_apps".ro开发者_开发知识库om_id = 4) ORDER BY created_at DESC LIMIT 1
  Color Load (0.4ms)  SELECT "colors".* FROM "colors" WHERE "colors"."id" = 4 LIMIT 1
  RoomApp Load (0.4ms)  SELECT "room_apps".* FROM "room_apps" WHERE "room_apps"."published" = 't' AND "room_apps"."id" = 4 AND ("room_apps".room_id = 3) ORDER BY created_at DESC LIMIT 1
  Color Load (0.3ms)  SELECT "colors".* FROM "colors" WHERE "colors"."id" = 3 LIMIT 1

Is something setup incorrectly? I'd like to be able to get suggested rooms and use includes for room_apps with one hit versus currently where it's a hit for every room.

Ideas? Thanks

I think you'll either want to use the full Rails3 arel interface like so:

@suggested_rooms = Room.includes(:room_apps).where(:suggested => true).limit(5)

Or do this for Rails 2.3x:

@suggested_rooms = Room.find_all_by_suggested(true, :include=>:room_apps).first(5)

Did some digging around and I think I have an idea what's going on.

include by default does not generate a single query. It generates N queries, where N is the number of models being included.

ruby-1.9.2-p180 :014 > Room.where(:suggested => true).includes(:room_apps => :colors)
  Room Load (0.5ms)  SELECT "rooms".* FROM "rooms" WHERE "rooms"."suggested" = 't'
  RoomApp Load (0.8ms)  SELECT "room_apps".* FROM "room_apps" WHERE "room_apps"."room_id" IN (1)
  Color Load (0.5ms)  SELECT "colors".* FROM "colors" WHERE "colors"."room_app_id" IN (1)

One exception to this is if you have a where clause that references one of the model tables being included, in this case it will use a LEFT OUTER JOIN to add the where clause to that table.

If you want to INNER JOIN a bunch of models AND include them, you have to use both joins and includes with the given models. joins alone will only do the INNER JOIN across the relations, includes will pull in the fields and setup the returned models with their relations intact.

ruby-1.9.2-p180 :015 > Room.where(:suggested => true).joins(:room_apps => :colors)
  Room Load (0.8ms)  SELECT "rooms".* 
                     FROM "rooms" 
                     INNER JOIN "room_apps" 
                       ON "room_apps"."room_id" = "rooms"."id"
                     INNER JOIN "colors" 
                       ON "colors"."room_app_id" = "room_apps"."id" 
                     WHERE "rooms"."suggested" = 't'

ruby-1.9.2-p180 :016 > Room.where(:suggested => true).joins(:room_apps => :colors).includes(:room_apps => :colors)
  SQL (0.6ms)  SELECT "rooms"."id" AS t0_r0, "rooms"."suggested" AS t0_r1, "rooms"."created_at" AS t0_r2, "rooms"."updated_at" AS t0_r3, "room_apps"."id" AS t1_r0, "room_apps"."room_id" AS t1_r1, "room_apps"."created_at" AS t1_r2, "room_apps"."updated_at" AS t1_r3, "colors"."id" AS t2_r0, "colors"."room_id" AS t2_r1, "colors"."room_app_id" AS t2_r2, "colors"."created_at" AS t2_r3, "colors"."updated_at" AS t2_r4 
               FROM "rooms" 
               INNER JOIN "room_apps" 
                 ON "room_apps"."room_id" = "rooms"."id" 
               INNER JOIN "colors" 
                 ON "colors"."room_app_id" = "room_apps"."id" 
               WHERE "rooms"."suggested" = 't'

The big convoluted SELECT part in the last query is ARel making sure that the fields from all of the models are unique and able to be differentiated when they need to be mapped back to the actual models.

Whether you use includes alone or includes with joins is a matter of how much data your bringing back, and how much speed difference there might be if you were not doing the INNER JOIN, causing a great deal of duplicate data to be returned. I would imagine that if 'rooms' had something like a dozen fields and 'colors' had 1 field, but there was 100 colors that mapped to a single room, instead of pulling back 113 fields in total (1 room * 13 + 100 colors * 1) you would end up with 1400 fields (13 + 1 * 100 colors). Not exactly a performance boost.

Though the downside of using includes alone is that if you do have a large number of colors per room, the IN(ids) will be huge, bit of a double edged sword.

Here's a quick test I did with various configurations using sqlite3

I setup two sets of rooms, one with :suggested => true, the other :suggested => false. The suggested rooms had a 1:1:2 ratio between rooms/room_apps/colors, the suggested false rooms were setup with a 1:1:10 ratio of the same, and there is a 10:1 ratio between suggested and not suggested.

# 100/10 rooms
# insert only
100 * 1/1/2: 8.1ms
10 * 1/1/10: 3.2ms

# insert + joins
100 * 1/1/2: 6.2ms
10 * 1/1/10: 3.1ms

# 1000/100 rooms
# insert only
1000 * 1/1/2: 76.8ms
100 * 1/1/10: 19.8ms

# insert + joins
1000 * 1/1/2: 54.5ms
100 * 1/1/10: 23.1ms

The times are not relevant themselves, this is being run via IRB on a Ubuntu guest on a WinXP host on a crappy HDD. Given that you've got a limit(5) in there it probably isn't going to make a huge difference either way.