Using Model method in SQlite3 and PostgreSQL

Question

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?

are you searching for an id that fits a format, or will you always have the full id? — Michael Gorman
– Michael Gorman, Commented Jun 14, 2017 at 20:02

spickermann · Accepted Answer · 2017-06-19 21:55:54Z

2

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

edited Jun 19, 2017 at 21:55

answered Jun 14, 2017 at 20:14

spickermann

108k9 gold badges115 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FEE Over a year ago

Hi @spickermann, thanks a lot for your suggestion. Your suggested format looks really clean. But can we call this from the controllers? Because I do call class method search from two different controllers. Is it possible with this too?

FEE Over a year ago

Sorry, for being late, it was a really busy weekend. But thanks a lot for your tips. Tried it. It works like a charm! Very short and to the point!

Hans Christian Cristobal · Accepted Answer · 2017-06-14 20:04:27Z

1

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

answered Jun 14, 2017 at 20:04

Hans Christian Cristobal

5885 silver badges9 bronze badges

1 Comment

FEE Over a year ago

It does exactly what I need.And much simpler way than what I expected. Thanks a lot for the solution.

Michael Gorman · Accepted Answer · 2017-06-14 20:09:30Z

0

Let the database adapter do the work for you

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

answered Jun 14, 2017 at 20:09

Michael Gorman

1,0777 silver badges13 bronze badges

Collectives™ on Stack Overflow

Using Model method in SQlite3 and PostgreSQL

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related