Filtering find by values in associated models?

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?

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.

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 <alias> on (<alias>.id =
customer.<alias>_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.

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/

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