LIKE query compatible with SQLite and PostgreSQL

1
votes

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?

2
ruby-on-railspostgresqlherokusqlitepattern-matching

2 Answers

1
votes

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

0
votes

Using Arel is an easy fix to this problem:

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