Polymorphic join query with conditions on both ends

Hi,

I have a polymorphic association between Company and Address.

class Address < ActiveRecord::Base   belongs_to :addressable, :polymorphic => true end

class Company < ActiveRecord::Base   has_many :addresses, :as => :addressable, :dependent => :destroy end

I want to fetch all addresses that fulfills conditions on both Company and Address. The following query does not work but it illustrates what I want to achieve.

Address.all :joins => :companies, :conditions => { :companies => { :reseller => true }, :addresses => { :geocoded => true } }

The following SQL query does the job but I want to do it with a single Active Record Query. Is that possible?

SELECT addresses.* FROM addresses INNER JOIN companies ON companies.id = addresses.addressable_id AND addresses.addressable_type = 'Company' WHERE (addresses.geocoded = 't'   AND addresses.address_type = 2   AND companies.reseller = 't'   AND companies.enabled = 't')

Hi,

I have a polymorphic association between Company and Address.

class Address < ActiveRecord::Base belongs_to :addressable, :polymorphic => true end

class Company < ActiveRecord::Base has_many :addresses, :as => :addressable, :dependent => :destroy end

I want to fetch all addresses that fulfills conditions on both Company and Address. The following query does not work but it illustrates what I want to achieve.

Address.all :joins => :companies, :conditions => { :companies => { :reseller => true }, :addresses => { :geocoded => true } }

As far as I know, you can't use a nested hash for :conditions

The following SQL query does the job but I want to do it with a single Active Record Query. Is that possible?

SELECT addresses.* FROM addresses INNER JOIN companies ON companies.id = addresses.addressable_id AND addresses.addressable_type = 'Company' WHERE (addresses.geocoded = 't' AND addresses.address_type = 2 AND companies.reseller = 't' AND companies.enabled = 't')

Well that's not the query I'd have expected from your nested hash condition if it worked, but something like this

Address.find(:all, :joins => :companies, :conditions => ['addresses.geocoded = ? AND addresses.address_type = ? andd companies.reseller = ? AND companies.enables = ', true, 2, true, true])

Might work.

Hi Rick and thanks for your answer,

The problem is not the nested hash but the fact that there is no assoiciation named :companies. Address belongs to :addressable... So my query should read:

Address.all :joins => :addressable, :conditions => { :companies => { :reseller => true }, :addresses => { :geocoded => true } }

But then I get an exception: ActiveRecord::EagerLoadPolymorphicError: Can not eagerly load the polymorphic association :addressable