0
User.where('user_id not in (?)', CancelledUser.all.collect(&:id).join(', '))

Above query gives me the following error when there are no cancelled users.

ActiveRecord::StatementInvalid: TinyTds::Error: Conversion failed when converting from a character string to uniqueidentifier.: EXEC sp_executesql N'SELECT [users].* FROM [userss] WHERE (user_id not in (N''''))'

How do i fix this?

usha
  • 28,973
  • 5
  • 72
  • 93

1 Answers1

0

You don't want to join the ids, that's going to be treated as a single string literal. Try the following: 

User.where('user_id not in (?)', CancelledUser.all.collect(&:id))

Update:

Executing the generated query in SQL Server directly also yields the same problem. Take a look at the answer in Issue when retrieving records with empty array for same problem when you have empty array.

So you could do the following to solve the issue: 

cancelled_users = CancelledUser.all.collect(&:id)

if cancelled_users.any?
  User.where('user_id not in (?)', cancelled_users)
else
  User.all
end
Community
  • 1
  • 1
vee
  • 38,255
  • 7
  • 74
  • 78
  • Above doesn't return any results when there are no cancelled user – usha Dec 05 '13 at 20:00
  • @Vimsha, I'm sure you've already resolved this issue. I've updated my answer with a possible solution. – vee Dec 10 '13 at 12:26