7

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
mu is too short
  • 426,620
  • 70
  • 833
  • 800
user714001
  • 133
  • 1
  • 5

3 Answers3

21

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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • 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! – fl00r Apr 24 '11 at 03:07
  • 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. – user714001 Apr 24 '11 at 03:09
  • just pass this job to Rails: `@msgs.where("deleted != ?", nil)`. It is like sql `IS NULL` condition – fl00r Apr 24 '11 at 03:10
  • 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. – user714001 Apr 24 '11 at 03:17
  • @fl00r: PostgreSQL uses [`'t'` and `'f'`](http://www.postgresql.org/docs/current/static/datatype-boolean.html) and allows a few other things too. – mu is too short Apr 24 '11 at 03:30
  • @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. – mu is too short Apr 24 '11 at 03:36
  • @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. – mu is too short Apr 24 '11 at 03:39
  • @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! – user714001 Apr 24 '11 at 03:45
  • @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 – fl00r Apr 24 '11 at 17:16
  • 1
    @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. – mu is too short Apr 24 '11 at 17:48
  • Hm. You are totaly right. `:deleted => nil` is what I was playing with. Thanks! – fl00r Apr 24 '11 at 17:55
  • @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](http://stackoverflow.com/questions/5709887/a-proper-way-to-escape-when-building-like-queries-in-rails-3-activerecord/5710095#5710095) inside a string that is going to a database would be good example of nested nonsense. – mu is too short Apr 24 '11 at 18:46
  • @mu, I know SQL (of course not briliant, but it fits my needs). But I don't understand what your answer is about :) – fl00r Apr 24 '11 at 18:52
3
@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)
fl00r
  • 82,987
  • 33
  • 217
  • 237
0

try 

@ms = @msgs.where(["deleted != ?",true])
Tam
  • 11,872
  • 19
  • 69
  • 119
  • 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? – user714001 Apr 24 '11 at 03:06
  • 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]) – Tam Apr 24 '11 at 05:32