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.