Rails - Jun 2009 - 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'')

On Tue, Jun 2, 2009 at 3:38 PM, Erik Dahlstrand
<erik.dahlstrand-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
> 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.


-- 
Rick DeNatale

Blog: http://talklikeaduck.denhaven2.com/
Twitter: http://twitter.com/RickDeNatale
WWR: http://www.workingwithrails.com/person/9021-rick-denatale
LinkedIn: http://www.linkedin.com/in/rickdenatale

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

On 2 Juni, 22:19, Rick DeNatale
<rick.denat...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On Tue, Jun 2, 2009 at 3:38 PM, Erik Dahlstrand
>
>
>
> <erik.dahlstr...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > 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.
>
> --
> Rick DeNatale
>
> Blog:http://talklikeaduck.denhaven2.com/
> Twitter:http://twitter.com/RickDeNatale
> WWR:http://www.workingwithrails.com/person/9021-rick-denatale
> LinkedIn:http://www.linkedin.com/in/rickdenatale