Query Rails 3 Active Record Fetch associated model based on conditions

Question

Here is what I have. A User model and an Address model. User has_many :addresses and Address belongs_to :user.

Now I want to search for users whose email contains a give term, or one of the user's addresses contains that term. So lets say the term is 'jap', any User whose email like '%jap%' or any user whose any address city or address_line is like '%jap%' should be returned.

I can get the Users with a simple join like this:

users = User.joins('LEFT OUTER JOIN addresses ON users.id=addresses.user_id').where('users.email like '%jap%' OR addresses.city like '%jap%' OR addresses.address_line like '%jap%')

then to get those addresses (if any) that matched the search term I have to query again for each user returned:

users.each do |u|
 u.addresses.where("addresses.city like '%jap%' OR addresses.address_line like '%jap%'")
end

Is there a way to improve this so that I only search the addresses table once? I mean, a single query that returns the Users and the matching addresses for each user.

Answer 1

User.joins(:addresses).where(["addresses.city like ? OR addresses.address_line like ?", '%jap%', '%jap%'])

or

a = Address.arel_table
User.joins(:addresses).where(a[:city].matches('%jap%').or(a[:address_line].matches('%jap%')))