Strange behaviour in ActiveRecord finder while using SQL condition IN

Question

I have a table called inbox_messages with following rows:

user_id message_id
======= ==========
4       8
4       1
4       7
0       9
0       10
0       11
0       12

The table maps to the following model:

class InboxMessage < ActiveRecord::Base
end

When I invoke the find method with a IN caluse, I get different resultset depending on the position of the parameters.

InboxMessage.find(:all, :conditions => [ "user_id IN (?)", "0,4"]) # 8 rows
InboxMessage.find(:all, :conditions => [ "user_id IN (?)", "4,0"]) # 3 rows

Second call above, returns 3 rows instead of 8. Other scenarios works fine, i.e.

InboxMessage.find(:all, :conditions => [ "user_id IN (0, 4)" ]) # 8 rows
InboxMessage.find(:all, :conditions => [ "user_id IN (4, 0)" ]) # 8 rows
InboxMessage.find(:all, :conditions => [ :user_id => [0, 4]  ]) # 8 rows
InboxMessage.find(:all, :conditions => [ :user_id => [4, 0]  ]) # 8 rows

Answer 1

The problem is that the literal is being passed into the SQL statement as a single literal.

InboxMessage.find(:all, :conditions => [ "user_id IN (?)", "0,4"])
becomes Select ... WHERE (user_id IN ('4,0'))

Try using two substitutions and it will work as you expect

InboxMessage.find(:all, :conditions => [ "user_id IN (?, ?)", 0,4])
becomes Select ... WHERE (user_id IN (4,0))

You can see what the actual SQL statement is by looking at the log in development mode.

Answer 2

This is a different form which I prefer to use because I find it more readable:

InboxMessage.find_all_by_user_id([0,4])