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

Question

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

`In the database they are stored as true/false`? As per as I know with sqlite boolean fields are stored with `t` or `f` values. Can you share the output of running `SELECT * FROM follow_ups LIMIT 5;"` in `rails dbconsole` — Kirti Thorat, Apr 22 '14 at 16:49
Does SQLite return your data when you call `FollowUp.where(is_complete: true)`? — user3356885, Apr 22 '14 at 17:33
Rails 5.2 has now a config option to specify this: `ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer` — Christoph Petschnig, Aug 30 '18 at 11:41

score 5 · Answer 1 · answered Oct 10 '14 at 11:04

5

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

answered Oct 10 '14 at 11:04

fuyi

2,573
4
23
46

Minor correction: MySQL does not have a native ("real") boolean data type; it maps it to TINYINT. – Marcus Jun 19 '16 at 09:23
Not at all transparent unfortunately. Try `if follow_up.is_compete` for a false value.. – thisismydesign Jan 31 '18 at 08:19

score 1 · Answer 2 · answered Jun 30 '16 at 15:39

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

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

2 Answers2