LIKE query compatible with SQLite and PostgreSQL

Question

I am trying to make a LIKE query work in my SQLite in development and PostgreSQL in production (Heroku) without using an if-else statement for either environment. If-else statement doesn't seem like a viable option.

My answer right now was based off the solution provided here: Generic Ruby solution for SQLite3 "LIKE" or PostgreSQL "ILIKE"?

This is what my course model looks like:

class Course < ApplicationRecord
  has_many :enrollments
  has_many :users, through: :enrollments

  has_many :course_subjects
  has_many :subjects, through: :course_subjects

  def self.search(search)
    where("lower(name) LIKE lower(?)", "%#{search}%") 
  end
end

How can I structure my LIKE query to be compatible with both SQLite and PostgreSQL?

score 1 · Answer 1 · answered Apr 06 '16 at 15:47

1

If I may suggest something, it would be adviseable to have the same DBs in all the environments, so that you are later able to have Postgresql-SQL parts in your code (for optimisation purposes and the like).

answered Apr 06 '16 at 15:47

born4new

1,677
12
12

I'd second this as well. Using different DBs in different environments isn't a good practice. – Anthony E Apr 06 '16 at 16:05

score 0 · Accepted Answer · answered Apr 07 '16 at 00:33

0

Using Arel is an easy fix to this problem:

def self.search(search)
    Course.where(Course.arel_table[:name].matches("%#{search}%"))
end

answered Apr 07 '16 at 00:33

spinyBabbler

392
5
19

LIKE query compatible with SQLite and PostgreSQL

2 Answers2