1 vs True vs 't'


#1

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?

Thanks a ton…


#2

I did solve my problem…

Table.count (:conditions => {approved => false} )


#3

yachtman wrote:

I did solve my problem…

Table.count (:conditions => {approved => 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.


#4

Robert W. wrote:

Table.count(:conditions => [ “approved = ?”, true ])

Well I meant Table.count(:conditions => [ “approved = ?”, false ]), but
you get the idea.


#5

Robert,

Thanks for the reply.

I imagine in this case, since I am only counting based on a boolean
value, there should be no worry about sanitization?

On Feb 8, 3:54 pm, Robert W. removed_email_address@domain.invalid


#6

On Feb 9, 2009, at 9:52 PM, yachtman wrote:

Table.count(:conditions => [ “approved = ?”, true ])

Well I meant Table.count(:conditions => [ “approved = ?”, false ]),
but
you get the idea.

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.

-Rob

Rob B. http://agileconsultingllc.com
removed_email_address@domain.invalid


#7

seems to work. I think rails is doing its thing either way.

issue = Issue.count(:conditions => {:published => false})
=> 3

[4;36;1mSQL (0.000348) [0m [0;1mSELECT count(*) AS count_all FROM
issues WHERE (issues.published = 0) [0m

On Mon, Feb 9, 2009 at 9:14 PM, Rob B.
removed_email_address@domain.invalidwrote:

wrote:
as for letting the database adapter fill in its own notion of what


Available for Ruby on Rails development opportunities