Michael Heath Michael Heath - 2 years ago 166
SQL Question

Rails where like query with constants


I have a searchbox that acts as a filter for selecting a customer based on First Name, Last Name, ID, and Full Name. I want to add Role to that as well but I want it to be an AND clause and I want it to be constant. My Users can either be 'customers' or 'propects' and I only want to display actual customers.


What is the best way to structure a Like query like so:

User.where('lower(first_name) LIKE ? OR lower(last_name) LIKE ? OR id % 100 = ? OR lower(full_name) LIKE ? AND role = ?', query, query, query_int, query, 'customer')


If this changes anything, I'm using PostgreSQL.

Answer Source

You could do something like:

User.where(role: 'customer')
    .where('lower(first_name) LIKE :q OR lower(last_name) LIKE :q OR lower(full_name) LIKE :q OR id % 100 = :qint', 
            q: "%#{query}%", 
            qint: query_int)

Since you're using Postgres you can use ILIKE (case-insensitive)

User.where(role: 'customer')
    .where('first_name ILIKE :q OR last_name ILIKE :q OR full_name ILIKE :q OR id % 100 = :qint', 
           q: "%#{query}%", 
           qint: query_int)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download