Complex ActiveRecord query with many joins

I'm creating some form to filter my users, table User and I'm discovering the joys of ActiveRecord.

• User has name and function columns.

• User has many Address

• Address has country and town columns.

• User has many Hobby

• Hobby has a catego开发者_如何学Cry column.

In my params I have name, function, country, town and category.

I basically want to get all Users who match at least one of these params (OR condition, I use a MySQL database).

Thanks in advance.

Off the top of my head, I think this will do it:

q = params[:query]

User.all(
  :conditions => ["
    users.name like ? or 
    users.function like ? or
    addresses.country like ? or
    addresses.town like ? or
    hobbies.category like ?
  ", q, q, q, q, q],
  :joins => [:hobbies, {:addresses => :country}]
)

Sometimes when you need to search across multiple tables you are better off denormalizing your data in an index. I had to do this over a year ago and chose to use a combination of Sunspot and Solr and this worked really well for me.

I copy some code below to let you see what I expected.

The query is built with OR and empty params are not included (neither their values, nor the join).

def self.filtering(name, country, category)
  query, join_array, query_string = self, [], []
  unless name.blank?
    query_string << " users.name = :name"
  end
  unless country.blank?
    query_string << "addresses.country = :country"
    join_array << :addresses
  end
  unless category.blank?
    query_string << "hobbies.category = :category"
    join_array << :hobbies
  end

  join_array.each do |q|
    query = query.joins(q)
  end

  q.where(query_string * " OR ", :name => name, :country => country, :category => category)
end