Proper use of the where method om ActiveRecord

Rails
1

I am trying to determine what is going on with the following code:

In AR
def self.all_billables
where( :is_billed => false )
end

returns 2 records.

def self.all_billables
where( “is_billed” => false )
end

also returns 2 records.

In sqlite3
sqlite> select * from tests where ( “is_billed” = ‘false’ );

returns 2 records.

However, if I use these forms in AR then I get nothing returned:

def self.all_billables
where( %Q( “is_billed” = ‘false’ ) )
end

def self.all_billables
where( “is_billed = ‘false’” )
end

What am I missing here?

2

On 13 November 2012 20:40, byrnejb [email protected] wrote:

where( “is_billed” => false )

def self.all_billables
where( %Q( “is_billed” = ‘false’ ) )
end

def self.all_billables
where( “is_billed = ‘false’” )
end

What am I missing here?

Look in log/development.log to see what the difference is in the
queries (which should be logged there). Then it will probably make
sense.

Colin

3

On Tuesday, November 13, 2012 3:45:30 PM UTC-5, Colin L. wrote:

Look in log/development.log to see what the difference is in the
queries (which should be logged there). Then it will probably make
sense.

This is the difference:

(0.2ms) SELECT COUNT() FROM “tests” WHERE (is_billed = ‘false’)
vice
(0.2ms) SELECT COUNT() FROM “tests” WHERE (is_billed = ‘f’)

The second form is generated from the ( :is_billed => false )
construction. If I cut and paste “is_billed = ‘f’” into the where
clause
then it works. I presume this is some sort of implementation issue with
sqlite3.

4

On 13 November 2012 21:05, byrnejb [email protected] wrote:

(0.2ms) SELECT COUNT() FROM “tests” WHERE (is_billed = ‘false’)
vice
(0.2ms) SELECT COUNT() FROM “tests” WHERE (is_billed = ‘f’)

The second form is generated from the ( :is_billed => false ) construction.
If I cut and paste “is_billed = ‘f’” into the where clause then it works. I
presume this is some sort of implementation issue with sqlite3.

By using :is_billed => false you are telling rails that you want the
value to be whatever rails uses as the logical value false (which
appears to be “f”). By specifying yourself that the contents of the
column must be the string “false” it does not find the records.

I am a bit surprised that the sqlite example you gave works. Are you
sure you entered the query exactly as you showed? I am not that
familiar with sqlite however.

Colin

5

On Tuesday, November 13, 2012 4:16:17 PM UTC-5, Colin L. wrote:

By using :is_billed => false you are telling rails that you want the
value to be whatever rails uses as the logical value false (which
appears to be “f”). By specifying yourself that the contents of the
column must be the string “false” it does not find the records.

I am a bit surprised that the sqlite example you gave works. Are you
sure you entered the query exactly as you showed? I am not that
familiar with sqlite however.

It appears that sqlite3 does not have a concept of a boolean type. I
find
that you can put literally any string into a boolean column in an
sqlite3
database. As I was testing in sqlite with data rows I had inserted
having
is_billed values ( ‘false’ ) selecting where ( “is_billed” = ‘false’ )
naturally worked. Ah well, live and learn.