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?
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
