Query condition to HABTM association not working

Question

I want to make a query joining two models, Devices and Users in a HABTM association:

@device = Device.left_outer_joins(:users).where(users: {id: nil || 4})

I am trying to get any users that are nil in the join table or have the id 4. The problem is that this query is ignoring the nil condition and generating this:

SELECT `devices`.* FROM `devices` LEFT OUTER JOIN `devices_users` ON `devices_users`.`device_id` = `devices`.`id` LEFT OUTER JOIN `users` ON `users`.`id` = `devices_users`.`user_id` WHERE `devices`.`serial` = '1111-1111-10' AND `users`.`id` = 4

What do I do to get this condition to be valid?

Answer 1

nil || 4 evaluates to 4, so that's why the query is ignoring nil.

If you change your query just slightly, it will work. Try this:

@device = Device.left_outer_joins(:users).where(users: {id: [nil, 4]})

ActiveRecord will take the array of [nil, 4] and translate it to the query you're looking for. For example, the SQL of your query would be something like this:

SELECT `devices`.* FROM `devices` LEFT OUTER JOIN `devices_users` ON `devices_users`.`device_id` = `devices`.`id` LEFT OUTER JOIN `users` ON `users`.`id` = `devices_users`.`user_id` WHERE `devices`.`serial` = '1111-1111-10' AND `users`.`id` = 4 OR `users`.`id` IS NULL

Also, this is a pretty neat part of ActiveRecord. You can even give it an array with more ids like this [nil, 4, 5] which will give you SQL like:

SELECT `devices`.* FROM `devices` LEFT OUTER JOIN `devices_users` ON `devices_users`.`device_id` = `devices`.`id` LEFT OUTER JOIN `users` ON `users`.`id` = `devices_users`.`user_id` WHERE `devices`.`serial` = '1111-1111-10' AND `users`.`id` IN (4, 5) OR `users`.`id` IS NULL