Using Model method in SQlite3 and PostgreSQL

0
votes

In my Rails app, I do have SQlite3 for development and PostgreSQL for production. I have a habit class with belongs to category and Category has many habits. So, to let user search any category to see associated habits, I have developed a class method in habit model which goes into database and find all the habits according to searched category.

So, if I write,

def self.search(search)
  where("category_id LIKE ?", search)
end

It works perfectly fine in development but PostgreSQL gives 500 error in production saying:

"No operator matches the given name and argument type(s). You might need to add explicit type casts."

So, I googled about it and found solution (Pgsql error: You might need to add explicit type casts) and edited my method accordingly:

def self.search(search)
  where("category_id::text LIKE ?", search)
end

Now, it works perfectly fine in preoduction but SQlite gives me an error in development saying:

"SQLite3::SQLException: unrecognized token: ":": SELECT "habits".* FROM "habits" WHERE "habits"."user_id" = ? AND (category_id::text LIKE '4')"

Tried a lot but couldn't solve this. Is there any way I can modify this method to work with both of the databases? or maybe is there any way I can tell my app to use which method in which environment?

3
ruby-on-railsrubypostgresqlsqlite
are you searching for an id that fits a format, or will you always have the full id? – Michael Gorman
Hi @MichaelGorman.. I will always have a category_id. – Hima Chhag

3 Answers

2
votes

Perhaps it is just me, but IMO there is not point in using LIKE on columns that contain an integer. Therefore I would just use a normal =:

def self.search(search)
  where("category_id = ?", search)
end

Because that doesn't follow common Rails idiom, let's simplify this to where with the hash syntax and use a scope instead of a class method:

scope :search, ->(search) { where(category_id: search) }

Which can be called from your controller like this:

Habit.search(params[:xyz])
1
votes

You can put a conditional on

ActiveRecord::Base.connection.instance_values["config"][:adapter]

Like

if ActiveRecord::Base.connection.instance_values["config"][:adapter] == "postgresql"
  puts "I'm using postgres!"
elsif ActiveRecord::Base.connection.instance_values["config"][:adapter] == "sqlite3"
  puts "I'm using sqlite!"
end
0
votes

Let the database adapter do the work for you

def self.search(search)
  where(category_id: search)
end