ActiveRecord::StatementInvalid SQLite3::SQLException: no such column: true:

Question

I want to have @messages return @folder.messages where the value of column "deleted" is NOT equal to true. I'm not sure why this keeps throwing a SQLException. I guess I'm not formatting the deleted attribute properly, but I'm not sure how to fix it.

Any help would be greatly appreciated. Thanks in advance.

Error message:

ActiveRecord::StatementInvalid in MailboxController#index  
SQLite3::SQLException: no such column: true: SELECT     "message_copies".* FROM       "message_copies"  WHERE     ("message_copies".folder_id = 1) AND (deleted != true)  

Application Trace:

app/controllers/mailbox_controller.rb:14:in `show'  
app/controllers/mailbox_controller.rb:5:in `index'  

Mailbox_Controller.rb

1   class MailboxController < ApplicationController  
2     def index  
3       current_user = User.find(session[:user_id])  
4       @folder = Folder.where("user_id = #{current_user.id}").first  
5       show  
6       render :action => "show"  
7     end
8  
9     def show  
10      current_user = User.find(session[:user_id])  
11      @folder = Folder.where("user_id = #{current_user.id}").first  
12      @msgs = @folder.messages  
13      @ms = @msgs.where("deleted != true")  
14      @messages = @ms.all.paginate :per_page => 10,  
15                 :page => params[:page], :include => :message,  
16                 :order => "messages.created_at DESC"  
17    end  
18  end  

Allard

Answer 1

SQLite uses C-style boolean values:

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

So, when you say this:

deleted != true

SQLite doesn't know what true is so it assumes you're trying to reference another column name.

The proper way to deal with this is to let AR convert your Ruby boolean to an SQLite boolean (as in Tam's and fl00r's answers). I think it is useful to know what you're doing wrong though.

UPDATE: If you want to check for non-true deleted and include NULL then you'll want this:

@ms = @msgs.where("deleted != ? OR deleted IS NULL", true)

Or better, don't allow NULLs in deleted at all. You shouldn't allow NULL is any column unless you absolutely have to (ActiveRecord's default for nullability is exactly the opposite of what it should be). The SQL NULL is an odd beast and you always have to treat it specially, best not to allow it unless you need a "not there" or "unspecified" value for a column.

Motivity

I thought it stores t/f pair. But I can be wrong :). But now I look into your link - I was wrong :) But somebody stores t/f. Definitely! – Supplejack

Thanks for the explanation. It was very helpful. Just to make sure I'm understanding sql correctly... how does it handle a nil value? By saying != 1 (true), will it also return rows with nil values? Thanks again. – Allard

just pass this job to Rails: @msgs.where("deleted != ?", nil). It is like sql IS NULL condition – Supplejack

i'm trying to return rows where deleted = false AND deleted = nil. I've tried @msgs.where(["deleted = ?", nil] || ["deleted = ?", false]) but it's still not returning rows where deleted is null. – Allard

@fl00r: PostgreSQL uses 't' and 'f' and allows a few other things too. – Motivity

@user714001: If you want to catch NULL values then you'll need to add an explicit deleted IS NULL to the WHERE clause, deleted = NULL shouldn't work in any database as x = NULL is false for all x (even if x is itself NULL). I added a quick update about NULL. – Motivity

@fl00r: I don't think @msgs.where("deleted != ?", nil) will work, wouldn't AR turn that into deleted != NULL? Any comparison to NULL has to be done with x IS NULL or x IS NOT NULL due to the unique characteristics of NULL. – Motivity

@mu is too short: Awesome! Thanks so much. I'm obviously not well versed in sql and db queries. Thank you thank you thank you! @fl00r: thank you as well. both of you are awesome! – Allard

@mu is too short, it will work. Because AR will handle it just the same as it handles ("deleted = ?", false). It will convert deleted = NULL to deleted IS NULL and deleted NOT NULL or deleted IS NOT NULL for 'NOT NULL' with different databases – Supplejack

@fl00r: Are you sure it will rewrite the SQL snippet? I just did a quick trace through the AR3 code and I see things like statement.gsub('?') but nothing that would change = to IS as a special case. I think sending in { :deleted => nil } would work though. – Motivity

Hm. You are totaly right. :deleted => nil is what I was playing with. Thanks! – Supplejack

@fl00r: No ORM can insulate you from SQL. If you're going to use a relational database, then you must understand SQL. SQL certainly isn't the best possible solution to the database problem but it is the solution we have to deal with. And, embedding one language inside another is always a bit of a mess, regex escaping inside a string that is going to a database would be good example of nested nonsense. – Motivity

@mu, I know SQL (of course not briliant, but it fits my needs). But I don't understand what your answer is about :) – Supplejack

Answer 2

@ms = @msgs.where("deleted != ?", true) 
# OR
@ms = @msgs.where(:deleted => false) 

true is different for different databases. In some it is t/f value, and in some true/false, so you should or place it in quotes and be sure if it is right for your particular database, or you should exclude it out of your sql so Rails will do the job for you.

UPD

If deleted is NULL. First. Set deleted field as a false by default. Second, how to find it with AR:

@ms = @msgs.where("deleted = ? OR deleted = ?", false, nil)
# wich won't work, Thanks to @mu is too short
@ms = @msgs.where("deleted = ? OR deleted IS NULL", false)

Supplejack

Answer 3

try

@ms = @msgs.where(["deleted != ?",true])

Antoniaantonie

Well it's not throwing the error anymore, so that's good. But it's still not returning the results I want. One more questions, as the statement is written... I'm assuming this should also return values where "deleted" isn't set (i.e. it doesn't equal true or false value) am I assuming correctly? – Allard

this one will only get you the ones that are true. If you want to return the ones that are null then do something like: @ms = @msgs.where(["deleted is NULL OR deleted != ?",true]) – Antoniaantonie