Deconstructing Rails .joins & .where methods

Question

I have two models - Banner and BannerType.

Their schema looks like this:

Banner

# Table name: banners
#
#  id             :integer          not null, primary key
#  name           :string(255)
#  image          :string(255)
#  created_at     :datetime         not null
#  updated_at     :datetime         not null
#  url            :string(255)
#  banner_type_id :integer

BannerType

# Table name: banner_types
#
#  id         :integer          not null, primary key
#  name       :string(255)
#  created_at :datetime         not null
#  updated_at :datetime         not null

Banner belongs_to :banner_type and BannerType has_many :banners

I have two records in BannerType that are these:

BannerType.all
  BannerType Load (0.3ms)  SELECT "banner_types".* FROM "banner_types" 
 => [#<BannerType id: 1, name: "Featured", created_at: "2012-12-17 04:35:24", updated_at: "2012-12-17 04:35:24">, #<BannerType id: 2, name: "Side", created_at: "2012-12-17 04:35:40", updated_at: "2012-12-17 04:35:40">] 

If I want to do a query to find all the banners of the type Featured I can do something like this:

Banner.joins(:banner_type).where("banner_types.name = ?", 'Featured')

I know that I could also query by the banner_type_id => 1 but that is germane to this particular question.

If we break down that statement, there are a few things that are a bit confusing to me.

1. Banner.join(:banner_type) - would generate NoMethodError: undefined method 'join' for #<Class:0x007fb6882909f0> Why is there no Rails method called join when that is the SQL method name?

2. Why do I do Banner.joins(:banner_type) i.e. the singular banner_type when the table name is banner_types. Am I not joining the Banner & BannerType tables (which Rails conventions denote as plural). If I try Banner.joins(:banner_types) this is the error that I get:

   Banner.joins(:banner_types) ActiveRecord::ConfigurationError: Association named 'banner_types' was not found; perhaps you misspelled it?

3. Why does the where clause need banner_types and not banner_type (i.e. the pluralized version - i.e. the table name and not the symbol used in the joins method? It seems it would be more intuitive if you use the table names in both places or use the symbol names in both places. If at the very least, for consistency purposes.

4. Why can't I do dynamic finding via associations - i.e. it would be nice if I could do Banner.find_by_banner_type_name("Featured")?

Would love to hear your thoughts.

Thanks.

Answer 1

#1

Banner.join(:banner_type) - would generate NoMethodError: undefined method 'join' for # Why is there no Rails method called join when that is the SQL method name?

It's clearer when read as plain English to say "Banner joins banner type" vs. "Banner join banner type". I'm not sure there's more of a reason than that.

---

#2

Why do I do Banner.joins(:banner_type) i.e. the singular banner_type when the table name is banner_types. Am I not joining the Banner & BannerType tables (which Rails conventions denote as plural). If I try Banner.joins(:banner_types) this is the error that I get:

Banner.joins(:banner_types) ActiveRecord::ConfigurationError: Association named 'banner_types' was not found; perhaps you misspelled it?

In .joins(:banner_type), :banner_type is the relation you're joining on, not the table. You have

has_one :banner_type

so that is what Rails joins on. This is just how Rails works when you pass a Symbol to .joins, and is why the error refers to the association when you pass a Symbol not matching any existing assocation for your model.

This is also why you're able to do JOIN's multiple levels deep using symbols for the nested associations, as described in the Rails Guide

Category.joins(:posts => [{:comments => :guest}, :tags])

Also described in the Rails Guide, you can pass Strings to .joins as well.

Client.joins('LEFT OUTER JOIN addresses ON addresses.client_id = clients.id')

Note in this last example, when a String is passed to joins, the table name addresses is used, not an association name; this helps answer #3.

---

#3

Why does the where clause need banner_types and not banner_type (i.e. the pluralized version - i.e. the table name and not the symbol used in the joins method? It seems it would be more intuitive if you use the table names in both places or use the symbol names in both places. If at the very least, for consistency purposes.

After a bit of string interpolation, Strings passed to the where method (similar to joins) are more or less passed directly into the final SQL query (there will be a bit of manipulation by ARel along the way). name is an ambiguous column (both your banners and banner_types tables have a name column), so referring to the table by it's full path [TABLE NAME].[COLUMN NAME] is required. If, for example you had some color column in banner_types (that didn't also exist in banners), there's no need to use it as "banner_types.color = ?" in your where method; "color = ?" will work just fine.

Note, just like in #2, you can pass symbols to the where method for JOIN'd tables.

Banner.joins(:banner_type).where(banner_type: [name: 'Featured'])

---

#4

Why can't I do dynamic finding via associations - i.e. it would be nice if I could do Banner.find_by_banner_type_name("Featured")?

You can't do a find like that because it's not supported in ARel, it's that simple really (IMO, a method name like find_by_banner_type_name quite confusing).