NOT IN (NULL) に苦しめられる昼下がり

Railsでアプリケーション開発していて、

some_batch.rb

@jobs = Job.where(company_id: user.companies.map(&:company_id))

とやりたくなることが多々あると思う。
ところがこいつ、危険である。この時はMySQLでやっていたのだが、

irb(main):201:0> jobs = Job.where(company_id: user.companies.map(&:company_id))
  Job Load (0.4ms)  SELECT `jobs`.* FROM `jobs` WHERE `jobs`.`company_id` IN (NULL)
=> []
irb(main):202:0> jobs = Job.where.not(company_id: user.companies.map(&:company_id))
  Job Load (0.4ms)  SELECT `jobs`.* FROM `jobs` WHERE (`jobs`.`company_id` NOT IN (NULL))
=> []

…えっ？

INの時もNOT INの時も空っぽになったよ！？

なぜなのか

注目すべきは、

SELECT `jobs`.* FROM `jobs` WHERE `jobs`.`company_id` IN (NULL)

と、

SELECT `jobs`.* FROM `jobs` WHERE (`jobs`.`company_id` NOT IN (NULL))

の2つのSQL。
IN (NULL)だろうがNOT IN (NULL)だろうが、どうやら結果はfalseになるらしいのだ。

解決方法としてはEXISTSを使うという手がある。

SELECT `jobs`.* FROM `jobs` WHERE EXISTS ( SELECT * FROM ……

が、Rails…というかActiveRecordにはEXISTSを扱うためのAPIは存在せず、whereの中にベタ書きしなければならない。

どっかの記事で誰かが「 ActiveRecordのキモは生SQLとARelをいかに上手く組み合わせるか 」的なことを書いていたけど、まぁ時間がない俺はもっと簡単な手を使うことにした。

どっちにしてもサブクエリの出番だ

jobs = Job.where(company_id: user.companies.select(:company_id))
jobs = Job.where.not(company_id: user.companies.select(:company_id))

はい。

こうすることで、

SELECT `jobs`.* FROM `jobs` WHERE `jobs`.`company_id` IN (SELECT company_id FROM `companies` INNER JOIN `company_users` ON `companies`.`id` = `company_users`.`company_id` WHERE `company_users`.`user_id` = 1)

こんなような感じのSQLを組み立てることが出来る。
ガチンコサブクエリなので、ループの中などでやろうとするとN+1問題が発生してサーバー負荷の高まりを感じるが、そうでなかったりするならむしろこっちで実装しておいて、時間ができたらリファクタリングする、という道をたどってもいいのではないか。