Filtering find by values in associated models?

Rails
1

Given

class Customer
belongs_to :invoice_address, :class_name => “Address”, …
belongs_to :delivery_address, :class_name => “Address”, …
belongs_to :contact, :class_name => “Employee”, …
end

How would you go about filtering a find :all where e.g.
invoice_address.city = “Oslo”, delivery_address.zip = 1234 and
contact.department = 14?
Exactly which and how many fields we want to filter on is highly
dynamic. Doing it in the db is a must.

Suggestions?

2

eager loading of associations should get in the right direction:

Customer.find(:all, :include => [:invoice_address, :delivery_address,
:contact], :conditions => [“Address.city=? AND Address.zip=? AND
contact.department=?”, “Oslo”, 1234, 14])

but this example wouldn’t do exactly what you want, since it’s mixing
invoice & delivery address.
it will take some work, to set this up the right way, maybe get a
working sql solution first.

3

On Wed, Apr 9, 2008 at 3:19 PM, Thorsten M.
[email protected] wrote:

working sql solution first.
We’re currently thinking of feeding :joins an sql fragment along the
lines of:
conditions.keys.substring(“.”) do |alias|
“inner join <alias_to_table_name> as on (.id =
customer._id)”
end.join(" ")

Which would use a condition hash like:
{“invoice_address.city” => “Oslo”, “delivery_address.zip” => “1234”,
…}

Unless someone can come up with a better approach.

4

I must be missing something.

Why can’t you simply eager-load the associations and do your
“invoice_address.city” => “Oslo” type conditions?

Julian.

Learn Ruby on Rails! Check out the FREE VIDS (for a limited time)
VIDEO #3 out NOW!
http://sensei.zenunit.com/

5

On 11 Apr 2008, at 00:41, Julian L. wrote:

I must be missing something.

Why can’t you simply eager-load the associations and do your
“invoice_address.city” => “Oslo” type conditions?

Because the table addresses is joined twice you’d need to know what it
was aliased as.

Fred