CODEWITHSUNDEEP
ruby-on-rails-4sqlite
Ryan-Neal Mes
Ryan-Neal Mes

Reputation: 6263

sqlite boolean 't' and 'f' with rails active record

I use mysql on production and sqlite3 on my development.

When querying my database on development e.g.

@follow_ups = FollowUp.where(is_complete: false)

I get the sql below in my console

SELECT "follow_ups".* FROM "follow_ups"  WHERE "follow_ups"."is_complete" = 'f'

sqlite evaluates 'f' as a truthy value so no follow_ups.is_complete = false are returned. In the database they are stored as true/false. From my investigations I found.

https://github.com/rails/rails/issues/10720
Rails 3 SQLite3 Boolean false

What should I do to get my boolean filters working? I would have thought this would be happening to more people.

See schema below

  create_table "follow_ups", force: true do |t|
    t.integer  "contact_id"
    t.integer  "owner_id"
    t.datetime "due_date"
    t.string   "comment"
    t.boolean  "is_complete",        default: false
    t.integer  "created_by_user_id"
    t.integer  "updated_by_user_id"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

See data and insertions below - done via rails console. As request in comments.

[30] pry(main)> FollowUp.create(contact_id: 1, due_date: Time.now, is_complete: true)
   (0.1ms)  begin transaction
  SQL (0.4ms)  INSERT INTO "follow_ups" ("contact_id", "created_at", "due_date", "is_complete", "updated_at") VALUES (?, ?, ?, ?, ?)  [["contact_id", 1], ["created_at", "2014-04-22 19:17:01.854402"], ["due_date", "2014-04-22 19:17:01.853540"], ["is_complete", "t"], ["updated_at", "2014-04-22 19:17:01.854402"]]
   (1.7ms)  commit transaction
#<FollowUp:0x0000010699c5a8> {
                    :id => 23,
            :contact_id => 1,
              :owner_id => nil,
              :due_date => Tue, 22 Apr 2014 19:17:01 UTC +00:00,
               :comment => nil,
           :is_complete => true,
    :created_by_user_id => nil,
    :updated_by_user_id => nil,
            :created_at => Tue, 22 Apr 2014 19:17:01 UTC +00:00,
            :updated_at => Tue, 22 Apr 2014 19:17:01 UTC +00:00
}
[31] pry(main)> FollowUp.where(is_complete: true)
  FollowUp Load (0.3ms)  SELECT "follow_ups".* FROM "follow_ups"  WHERE "follow_ups"."is_complete" = 't'
#<ActiveRecord::Relation [#<FollowUp id: 16, contact_id: 1, owner_id: 1, due_date: "2014-04-23 00:00:00", comment: "Lorem ipsum dolor sit amet, consectetur adipisicin...", is_complete: true, created_by_user_id: 1, updated_by_user_id: 1, created_at: "2014-04-17 09:57:00", updated_at: "2014-04-22 14:37:36">, #<FollowUp id: 23, contact_id: 1, owner_id: nil, due_date: "2014-04-22 19:17:01", comment: nil, is_complete: true, created_by_user_id: nil, updated_by_user_id: nil, created_at: "2014-04-22 19:17:01", updated_at: "2014-04-22 19:17:01">]>
[32] pry(main)> FollowUp.where(is_complete: false)
  FollowUp Load (0.2ms)  SELECT "follow_ups".* FROM "follow_ups"  WHERE "follow_ups"."is_complete" = 'f'
#<ActiveRecord::Relation []>
[33] pry(main)> FollowUp.all
  FollowUp Load (0.2ms)  SELECT "follow_ups".* FROM "follow_ups"
#<ActiveRecord::Relation [#<FollowUp id: 16, contact_id: 1, owner_id: 1, due_date: "2014-04-23 00:00:00", comment: "Lorem ipsum dolor sit amet, consectetur adipisicin...", is_complete: true, created_by_user_id: 1, updated_by_user_id: 1, created_at: "2014-04-17 09:57:00", updated_at: "2014-04-22 14:37:36">, #<FollowUp id: 17, contact_id: 1, owner_id: 1, due_date: "2014-04-24 00:00:00", comment: "This ia  long comment", is_complete: false, created_by_user_id: 1, updated_by_user_id: 1, created_at: "2014-04-17 10:04:13", updated_at: "2014-04-17 10:04:13">, #<FollowUp id: 18, contact_id: 1, owner_id: 1, due_date: "2014-04-24 00:00:00", comment: "This is a comment\r\n", is_complete: false, created_by_user_id: 1, updated_by_user_id: 1, created_at: "2014-04-17 10:24:05", updated_at: "2014-04-17 10:24:05">, #<FollowUp id: 19, contact_id: 1, owner_id: 1, due_date: "2014-04-23 00:00:00", comment: "test", is_complete: false, created_by_user_id: 1, updated_by_user_id: 1, created_at: "2014-04-22 13:37:40", updated_at: "2014-04-22 13:37:40">, #<FollowUp id: 20, contact_id: 1, owner_id: 1, due_date: "2014-04-23 00:00:00", comment: "test", is_complete: false, created_by_user_id: 1, updated_by_user_id: 1, created_at: "2014-04-22 13:37:41", updated_at: "2014-04-22 13:37:41">, #<FollowUp id: 21, contact_id: 1, owner_id: 1, due_date: "2014-04-24 00:00:00", comment: "test", is_complete: false, created_by_user_id: 1, updated_by_user_id: 1, created_at: "2014-04-22 13:39:20", updated_at: "2014-04-22 13:39:20">, #<FollowUp id: 22, contact_id: 2, owner_id: 1, due_date: "2014-04-30 00:00:00", comment: "test", is_complete: false, created_by_user_id: 1, updated_by_user_id: 1, created_at: "2014-04-22 13:53:37", updated_at: "2014-04-22 13:53:37">, #<FollowUp id: 23, contact_id: 1, owner_id: nil, due_date: "2014-04-22 19:17:01", comment: nil, is_complete: true, created_by_user_id: nil, updated_by_user_id: nil, created_at: "2014-04-22 19:17:01", updated_at: "2014-04-22 19:17:01">]>

Upvotes: 4

Views: 2769

Answers (2)

Lex Lindsey
Lex Lindsey

Reputation: 531

Using sqlite3 in Rails 4.2.5, I find that a boolean field returns ruby true or false when queried from Rails console. However, when queried inside a controller method, it returns a "t" or "f" string. Very weird. So I added a model method:

def fixt?
    return self[:fixt] == "t"
end

Schema:

 t.boolean  "fixt",                  default: false

Upvotes: 1

fuyi
fuyi

Reputation: 2639

In sqlite3, boolean value is not supported. So rails use char 't' and 'f' to represent boolean value true and false. while in other database like mysql, postgresql, the real boolean value true and false is used.

However, this difference is transparent to rails Model. you can just use

FollowUp.where(is_compete: true)

to filter completed followups and

FollowUp.where(is_compete: false)

to get incomplete followups

Upvotes: 5

Related Questions