setting find conditions to equal a value 2 tbl joins away

rubyonrails-talk
1

ok here is my initial table setups:

ITEM TYPES id name

ITEMS id name item_type_id

TOOLS id item_id parent_id

class Tool < ActiveRecord::Base     belongs_to :item

    def name       item.name     end

    def item_type       item.item_type.name     end end

class Item < ActiveRecord::Base     belongs_to :item_type end

I can use the following tool.item_type or item.item_type.name to get the name of the item type but i can not get this query to work:

@tools = Tool.find(:all, :include => :item, :conditions => { :item_type => "Instrument"}, :order => :name)

I am getting Unknown column 'tools.item_type' even though item_type is defined in the model. any ideas?

thanks!

2

class Tool < ActiveRecord::Base    belongs_to :item

   def name      item.name    end

   def item_type      item.item_type.name    end end

class Item < ActiveRecord::Base    belongs_to :item_type end

I can use the following tool.item_type or item.item_type.name to get the name of the item type but i can not get this query to work:

@tools = Tool.find(:all, :include => :item, :conditions =>
{ :item_type => "Instrument"}, :order => :name)

I am getting Unknown column 'tools.item_type' even though item_type is defined in the model. any ideas?

conditions need to reference database columns. methods you've defined
don't count. :conditions => {'items.item_type' => 'Instrument'} should work.

Fred

3

Frederick Cheung wrote:

   end { :item_type => "Instrument"}, :order => :name)

I am getting Unknown column 'tools.item_type' even though item_type is defined in the model. any ideas?

conditions need to reference database columns. methods you've defined don't count. :conditions => {'items.item_type' => 'Instrument'} should work.

Fred

ah ok thanks. since item_type isn't directly a database column but item_type_id is i can get this statement working:     @tools = Tool.find(:all, :include => :item, :conditions => {'items.item_type_id' => 2}, :order => :name)

but what if i want to go one level deeper like

    @tools = Tool.find(:all, :include => :item, :conditions => {'items.item_types.name' => 'Instrument'}, :order => :name)

or

    @tools = Tool.find(:all, :include => :item, :include => :item_type, :conditions => {'item_types.name' => 'Instrument'}, :order => :name)

the first give me an error saying Unknown column 'items.item_types.name' the second gives me the error Association named 'item_type' was not found.

thanks for the help so far. i think i'm almost there!

4

There's 2 separate things going on here. The first is including/joining the right tables (by the way, do you need :include or is :joins enough) if you need to join item and then join item_types onto that then you need to do :include => {:item => :item_type}. The second bit is the condition, where you just need to provide the disambiguated column name ie {'item_types.name' => 'Instrument'}

Fred