Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Implementing multiple joins with Arel
# The problem: In your Rails 3 project, you have a model Request that models a
# user's request for content related to a specific problem or topic. The user
# can tag her request with any number of words. These are modeled by a Tag class
# backed by a tags DB table. Since you want unique records per tag, you have a
# Tagging class backed by a taggings table. Taggings is a a many-to-many table
# with some additional information. Also, other models in the application
# besides requests can be tagged: The Tagging class defines a polymorphic
# relationship "taggable" with those models.
#
# So pretty soon you want to look up all the requests that are tagged with
# certain words, say "bunnies" and "bacon".
#
# You can do that with raw SQL:
Request.find_by_sql("
SELECT * FROM requests
INNER JOIN taggings ON taggings.taggable_id = requests.id AND taggings.taggable_type = 'Request'
INNER JOIN tags ON taggings.tag_id = tags.id
WHERE tags.contents = 'bunnies' OR tags.contents = 'bacon'
")
# But you are using Rails 3. You have Arel. So how do you do the chained inner
# joins from requests to taggings and then taggings to tags?
# With Arel
Request.joins(:taggings).
where(:taggings => {
:id => Tagging.joins(:tag).
where(:tags => {:contents => ["bunnies","bacon"]})
})
# You probably do not want duplicate Request records
Request.joins(:taggings).
where(:taggings => {
:id => Tagging.joins(:tag).
where(:tags => {:contents => ["bunnies","bacon"]})
}).uniq
@slayer

This comment has been minimized.

Copy link

commented Feb 27, 2011

funny, but this is not a multiple join, sorry

@remixlearning

This comment has been minimized.

Copy link

commented Apr 21, 2011

Does this work? Is there documentation on such syntax anywhere?

@slayer

This comment has been minimized.

Copy link

commented Apr 21, 2011

yes, it should work, but its not real join - its subquery.
if you want real JOIN - look at https://gist.github.com/935641

@sylvaindesve

This comment has been minimized.

Copy link

commented Apr 27, 2011

How would you do to find requests tagged with "bunnies" and "bacon" ?

@towfiq

This comment has been minimized.

Copy link

commented Jan 20, 2014

How about (not tested):

Request.joins(taggings: :tags).
  where(taggings: {tags: {contents: ["bunnies","bacon"]}})
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.