I'm using SQLite3, but have been a longtime MySQL user. I have been
wracking my brain over why I was getting a "0" result for a count when
it should be "10".
The statement....
Table.count (:conditions => "approved = 0")
The result in SQLite3: 0
The result in MySQL: 10
The corrected (I think) statement for SQLite:
Table.count (:conditions => "approved = 'f' ")
I am surprised and wonder why SQLite using the 'f' and 't'
nomenclature when '0' and '1' have worked so well....
Since this specificity would rule out me easily switching my DB back
to SQL, how can I write my command to use True and False?
Or this will work as well:
Table.count(:conditions => [ "approved = ?", true ])
Putting the value in the conditions string doesn't gives Rails a chance
to process it. Instead it just goes directly into the WHERE clause as
is. Both the hash version and the value substitution using the question
mark version allow Rails to process and properly sanitize the SQL.
You're not doing ['approved = ?',false] for the sanitization as much as for letting the database adapter fill in its own notion of what value it uses for false. Even if you were doing more than counting, you have control of the inputs so there's no SQL injection here.