Michael Heath Michael Heath - 2 months ago 12
SQL Question

Rails where like query with constants

Background:

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.

Question:

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')


Notes:

If this changes anything, I'm using PostgreSQL.

Answer

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)
Comments