Filtering find by values in associated models?

rubyonrails-talk
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

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.

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

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