SQL in Rails - Chaining "AND"

Question

Hi,

I don't get why SQL does not like my query:

@unreadmessages = Message.find(:all, :conditions => ["receiver_id = ? AND receiver_archived = ? AND read = ?", self.current_user, "No", "No"])

There is a different way to write this, but I want to keep my style consistent (this one avoids SQL injections, I gather.).

Thanks for your input!

Answer 1

"Read" is a reserved word in MySQL. I recommend renaming the column. Alternatively you can put it in back-tics every time it is referenced.

@unreadmessages = Message.find(:all, :conditions => ["receiver_id = ? AND receiver_archived = ? AND `read` = ?", self.current_user, "No", "No"])

I recommend renaming though.

Answer 2

I'm going to assume that you're not really storing the word "No" in your database to track true/false. If you're using a boolean or bit, try this.

@unreadmessages = Message.find(:all, :conditions => ["receiver_id = ? AND receiver_archived = ? AND read = ?", self.current_user.id, false, false])

Edit: See what ryanb says about "read," and please consider what I said about your columns if this is a new app and you have control over what the database schema looks like.