0

I have the following squeel query:

i = Invoice.where{ paid == true }

that's the same as:

i = Invoice.where ['paid = ?', true]

and executes:

SELECT "invoices".* FROM "invoices" WHERE "invoices"."paid" = 't'

However, this query doesn't return any invoices at all. It doesn't work if I try to execute the query from my sqlite program ether, seems like that query is wrong. I'm absolutely sure there are invoices in the sqlite db with both 't' and 'f' as value. How to get this right?

Jasper Kennis
  • 3,225
  • 6
  • 41
  • 74

2 Answers2

1

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true). Try this:

i = Invoice.where{ paid == 1 }

Also see: http://www.sqlite.org/datatype3.html

UPDATE:

I found a great explanation for your dilemma right here at SO. See Rails 3 SQLite3 Boolean false.

Good luck!

Community
  • 1
  • 1
Anil
  • 3,899
  • 1
  • 20
  • 28
  • Rails stores true as 't' and false as 'f'. I don't know why that is, but the query generated by squeel should therefor be correct, however still doesn't fetch anything. I tried querying with 0 and 1, but it's no good. – Jasper Kennis Jun 15 '12 at 12:06
  • @JasperKennis I found a great explanation for your dilemma right here at SO. See `http://stackoverflow.com/questions/6013121/rails-3-sqlite3-boolean-false`. – Anil Jun 15 '12 at 12:09
  • That's some ugly trouble:( I hope there's a better solution, I'll just leave it for a little while, glad to know that at least my production server using MySQL will probably understand the query. – Jasper Kennis Jun 15 '12 at 12:15
-2

I believe the correct syntax should be:

 i = Invoice.where(paid: true)  # corrected from using braces to parenthesis
gabrielhilal
  • 10,660
  • 6
  • 54
  • 81
pepe
  • 555
  • 4
  • 8
  • No, that doesn't make sense. Tested it anyway, but `i = Invoice.where{ paid: true }.length` returns syntax errors. – Jasper Kennis Jun 15 '12 at 12:03
  • Sorry, the syntax was wrong, I think, from the start. You need to use parenthesis, not braces: i = Invoice.where(paid: true). This is assuming 'paid' is a boolean column in your table. – pepe Jun 15 '12 at 12:21
  • Strange enough, after editing entities, the values seemed to finally become readable as booleans, and queriable the squeel way too. However, your method seemed to word before updating too, seems really strange to me... – Jasper Kennis Jun 15 '12 at 12:41