CODEWITHSUNDEEP
ruby-on-railsrails-activerecord
TheSuper
TheSuper

Reputation: 645

Eager Loading has_many though on a belongs_to relationship

I have some models like this:

class Image < ActiveRecord::Base
  has_many :tag_groups
end

class TagGroup < ActiveRecord::Base
  belongs_to :image
  has_many :tag_group_members
  has_many :tags, through: :tag_group_members
end

class TagGroupMember < ActiveRecord::Base
  belongs_to :tag_group
  belongs_to :tag
end

class Tag
  has_many :tag_group_members
  has_many :tag_groups, through: :tag_group_members
end

I want to select all the tag groups, and their tags, for an image. The obvious solution is something like:

Image
  .includes(tag_groups: :tags)
  .find(params[:id])

However, iterating through that (@image.tag_groups.map{|g| g.tags.map{|t| t.name}}), we find that Rails is making a rather large number of SQL queries:

TagGroup Load (0.8ms)  SELECT "tag_groups".* FROM "tag_groups" WHERE "tag_groups"."image_id" IN (1)
  Tag Exists (0.3ms)  SELECT  1 AS one FROM "tags" INNER JOIN "tag_group_members" ON "tags"."id" = "tag_group_members"."tag_id" WHERE "tag_group_members"."tag_group_id" = $1 LIMIT 1  [["tag_group_id", 1]]
  Tag Load (0.3ms)  SELECT "tags".* FROM "tags" INNER JOIN "tag_group_members" ON "tags"."id" = "tag_group_members"."tag_id" WHERE "tag_group_members"."tag_group_id" = $1  [["tag_group_id", 1]]
  Tag Exists (0.3ms)  SELECT  1 AS one FROM "tags" INNER JOIN "tag_group_members" ON "tags"."id" = "tag_group_members"."tag_id" WHERE "tag_group_members"."tag_group_id" = $1 LIMIT 1  [["tag_group_id", 3]]
  Tag Load (0.3ms)  SELECT "tags".* FROM "tags" INNER JOIN "tag_group_members" ON "tags"."id" = "tag_group_members"."tag_id" WHERE "tag_group_members"."tag_group_id" = $1  [["tag_group_id", 3]]
  Tag Exists (0.6ms)  SELECT  1 AS one FROM "tags" INNER JOIN "tag_group_members" ON "tags"."id" = "tag_group_members"."tag_id" WHERE "tag_group_members"."tag_group_id" = $1 LIMIT 1  [["tag_group_id", 4]]
  Tag Load (0.6ms)  SELECT "tags".* FROM "tags" INNER JOIN "tag_group_members" ON "tags"."id" = "tag_group_members"."tag_id" WHERE "tag_group_members"."tag_group_id" = $1  [["tag_group_id", 4]]

Now, it seems like this could be handled fairly easily in just three queries:

SELECT images.* FROM images WHERE images.id = $1

And then:

SELECT tag_groups.* FROM tag_groups WHERE tag_groups.image_id = $1

And, finally:

SELECT tags.*, tag_groups.id AS tag_group_id FROM tags
INNER JOIN tag_group_members ON tag_group_members.tag_id = tags.id
INNER JOIN tag_groups ON tag_groups.id = tag_group_members.tag_group_id
WHERE tag_groups.image_id = $1 -- alternatively, where tag_groups.id IN (results from the previous query)

You can then simply associate the tags to their proper tag-groups in memory, which should be much faster then querying the database for each tag group.

Is there any way to get Rails to load this association without doing that many joins?

Upvotes: 1

Views: 55

Answers (2)

smathy
smathy

Reputation: 27971

You don't even need the second query you mentioned because you already have tag_groups.image_id in your join query:

i = Image.find params[:id]
tgs = i.tag_groups.joins(:tags)

Update

Interesting, I didn't realize that joins didn't also eager load the associations in memory, seems that you'd need to add an .includes(:tags) to the above code to prevent any other queries being run.

Also, this also accomplishes the same thing but in a single step:

i = Image.joins(tag_groups: :tags).includes(tag_groups: :tags).find 1

Upvotes: 1

Alejandro Babio
Alejandro Babio

Reputation: 5229

Just:

@image = Image.includes(tag_groups: {tag_group_members: :tags}).find(param[:id])

First call include from Image, that returns a ActiveRecord::Relation, after that you can call find.

An this will do the trick.

Upvotes: 0

Related Questions