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

Question

I want @messages to return @ folder.messages, where the value of the "deleted" column is not true. I am not sure why this continues to throw an SQLException. I think I did not format the deleted attribute properly, but I am 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 

Answer 1

SQLite uses C-style booleans :

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 does not know what true , so it is assumed that you are trying to refer to a different column name.

The right way to handle this is to let AR convert your Ruby boolean into SQLite boolean (as in Tam and fl00r answers). I think it’s useful to know what you are doing wrong, though.

UPDATE If you want to check for invalid deleted and enable NULL, you will need the following:

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

Or better, don't allow NULL in deleted at all. You should not allow NULL - this is any column unless you absolutely need it (the default ActiveRecord value for nullability is exactly the opposite of what it should be). SQL NULL is a strange beast, and you should always handle it on purpose, it’s better not to allow it unless you need the value “nonexistent” or “indefinite” for the column.

Answer 2

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

true is different for different databases. In some this value is t/f , and in some it is true/false , so you must either put it in quotation marks and be sure that it is suitable for your specific database, or you must exclude it from your sql, so Rails will do the work for you.

UPD

If deleted - NULL . First. Set the field to be deleted as false by default . Secondly, 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) 

Answer 3

try

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