Help with a Join in Rails 3

Question

I have the following models:

class Event < ActiveRecord::Base
  has_many :action_items
end

class ActionItem < ActiveRecord::Base
  belongs_to :event
  belongs_to :action_item_type
end

class ActionItemType < ActiveRecord::Base
  has_many :action_items
end

And what I want to do is, for a given event, find all the action items that have an action item type with a name of "foo" (for example). So I think the SQL would go something like this:

SELECT * FROM action_items a
INNER JOIN action_item_types t
ON a.action_item_type_id = t.id
WHERE a.event_id = 1
AND t.name = "foo"

Can anybody help me translate this into a nice active record query? (Rails 3 - Arel)

Thanks!

Solution 1

Well I think I solved it myself. Here's what I did

e = Event.find(1)
e.action_items.joins(:action_item_type).where("action_item_types.name = ?", "foo")

Solution 2

Ehm, why not define

has_many :action_item_types, :through => :action_items

and refer to

e.action_item_types.where(:name => "foo")

?

Solution 3

or (as long as "name" is a unique column name)

e.action_items.joins(:action_item_type).where(:name => "foo")

Comments

[Marc]

If you wanted to make it even nicer looking you can do e.action_items.joins(:action_item_type).where(:action_item_t‌​ypes => {:name => "foo"})

[Gayle]

I also noticed that if you pass a String, not a Symbol, to your joins, it doesn't form the query correctly. e.g. do Event.joins(:action_item_type) NOT Event.joins("action_item_type")

[cyrilchampier]

Does this works in Rails4 ? subscriptions.joins(:plan).where(:plans => { :is_free_trial => true }) return Unknown column 'plans.is_free_trial' in 'where clause'