2
\$\begingroup\$

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.

\$\endgroup\$
1
\$\begingroup\$
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%')))
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.