CODEWITHSUNDEEP
sqlruby-on-railspostgresqlactiverecordsqlite
Torrm
Torrm

Reputation: 153

Correct format for a query on a Boolean within a Rails app -- Targetting SQLite / PostgreSQL

As the title states, what is the correct format when using a where clause within a rails app that targets multiple databases (SQLite for development, PostgreSQL for production)?

I have a dropdown list within a view:

<div class="form-group">
<%= f.label :category, :class => 'control-label col-lg-2' %>
<div class="col-lg-10">
  <%= f.select(:category_id, Category.where(user_accessible: true).collect {|p| [ p.name, p.id ] }, {  }, { :class => "dropdown" }) %>
</div>

..and the schema for clarity

create_table "categories", force: :cascade do |t|
    t.string   "name"
    t.datetime "created_at",                     null: false
    t.datetime "updated_at",                     null: false
    t.boolean  "user_accessible", default: true
end

This where clause is problematic, having tried a number of solutions on Stack Overflow, I still have not found a solution that works on both target databases.

A few examples of what I have tried

Category.where(user_accessible: true)

Works on PostgreSQL, Returns an empty result set on SQLite

Category.where("user_accessible = 1")

Works on SQLite, Throws an error on PostgreSQL (ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR: operator does not exist: boolean = integer) 

Category.where("user_accessible = true")

Works on PostgreSQL, Throws an error on SQLite (ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: true:)

Category.where("user_accessible = ?", true)

Works on PostgreSQL, returns empty result set on SQLite

Category.where("user_accessible = ?", 't')

Works on PostgreSQL, returns empty result set on SQLite

Surely this is a common setup for rails and also a common scenario? Am I missing something blindly obvious?

Thank you in advance

Upvotes: 0

Views: 287

Answers (1)

CL.
CL.

Reputation: 180010

To generate a boolean value, use a boolean expression with a known result:

Category.where("user_accessible = (1 = 1)")

If you want an actual value to be used as a parameter, you can read it from the database:

 ... = ActiveRecord::Base.connection.execute("SELECT 1=1")

Upvotes: 1

Related Questions