How to express a NOT IN query with ActiveRecord Rails

Question

I m hoping there is a easy solution that doesn t involve find by sql  if not then I guess that will have to work  I found this article which references this  Topic find  all   conditions   gt     forum id   gt   forums map  amp  id      which is the same as SELECT   FROM topics WHERE forum id IN   lt  forum ids gt    I am wondering if there is a way to do NOT IN with that  like  SELECT   FROM topics WHERE forum id NOT IN   lt  forum ids gt

User · Answer

This way optimizes for readability  but it s not as efficient in terms of database queries     Retrieve all topics  then use array subtraction to   find the ones not in our list Topic all -  forums map  amp  id

User · Answer

Using Arel   topics Topic arel table Topic where topics  forum id  not in  forum ids     or  if preferred   topics Topic arel table Topic where topics  forum id  in  forum ids  not    and since rails 4 on   topics Topic arel table Topic where not topics  forum id  in  forum ids     Please notice that eventually you do not want the forum ids to be the ids list  but rather a subquery  if so then you should do something like this before getting the topics    forum ids   Forum where   whatever conditions are desirable    select  id    in this way you get everything in a single query  something like   select   from topic  where forum id in  select id                     from forum                     where   whatever conditions are desirable      Also notice that eventually you do not want to do this  but rather a join - what might be more efficient

User · Answer

If someone want to use two or more conditions  you can do that  your array    1 2 3 4  your string    quot SOMETHING quot   YourModel where  variable1 NOT IN     AND variable2      Array wrap your array  your string

User · Answer

When you query a blank array add   lt  lt  0  to the array in the where block so it doesn t return  NULL  and break the query   Topic where  id not in      actions  lt  lt  0    If actions could be an empty or blank array

User · Answer

To expand on  Trung L   answer  in Rails 4 you can do the following   Topic where not forum id  forums map  amp  id     And you could take it a step further  If you need to first filter for only published Topics and then filter out the ids you don t want  you could do this   Topic where published true  where not forum id  forums map  amp  id     Rails 4 makes it so much easier

User · Answer

Can these forum ids be worked out in a pragmatic way  e g  can you find these forums somehow - if that is the case you should do something like  Topic all  joins   gt   left join forums on  forums id   topics forum id and some condition     conditions   gt   forums id is null     Which would be more efficient than doing an SQL not in

User · Answer

The original post specifically mentions using numeric IDs  but I came here looking for the syntax for doing a NOT IN with an array of strings   ActiveRecord will handle that nicely for you too   Thing where   state NOT IN        w state1 state2

User · Answer

The accepted solution fails if  forums is empty  To workaround this I had to do  Topic find  all   conditions   gt    forum id not in         forums empty          forums map  amp  id       Or  if using Rails 3    Topic where   forum id not in         forums empty          forums map  amp  id     all

User · Answer

Most of the answers above should suffice you but if you are doing a lot more of such predicate and complex combinations check out Squeel  You will be able to doing something like    Topic where  forum id not in   gt   forums map  amp  id    Topic where forum id not in  forums map  amp  id    Topic where forum id  lt  lt   forums map  amp  id

User · Answer

Rails 4    Article where not title    Rails 3    Rails 5       Rails 3   Topic where  id NOT IN       Array wrap actions     Where actions is an array with   1 2 3 4 5

User · Answer

Piggybacking off of jonnii   Topic find  all   conditions   gt    forum id not in        forums pluck  id      using pluck rather than mapping over the elements  found via railsconf 2012 10 things you did not know rails could do

User · Answer

You may want to have a look at the meta where plugin by Ernie Miller  Your SQL statement   SELECT   FROM topics WHERE forum id NOT IN   lt  forum ids gt        could be expressed like this   Topic where  forum id nin   gt   forum ids    Ryan Bates of Railscasts created a nice screencast explaining MetaWhere   Not sure if this is what you re looking for but to my eyes it certainly looks better than an embedded SQL query

User · Answer

FYI  In Rails 4  you can use not syntax   Article where not title    Rails 3    Rails 5

User · Answer

Here is a more complex  not in  query  using a subquery in rails 4 using squeel  Of course very slow compared to the equivalent sql  but hey  it works       scope  translations not in english  - gt  calmapp version id  language iso code         join to cavs tls arr calmapp version id         joins to tl arr        where  tl1 iso code     en           where  cavtl1 calmapp version id    my calmapp version id          where  dot key code  lt  lt   Translation          join to cavs tls arr calmapp version id           joins to tl arr              where  tl1 iso code    my language iso code             select   dot key code    all           The first 2 methods in the scope are other scopes which declare the aliases cavtl1 and tl1   lt  lt  is the not in operator in squeel   Hope this helps someone

User · Answer

You can try something like   Topic find  all   conditions   gt    forum id not in        forums map  amp  id      You might need to do  forums map  amp  id  join        I can t remember if Rails will the argument into a CSV list if it is enumerable   You could also do this     in topic rb named scope  not in forums  lambda    forums     conditions   gt    forum id not in       forums select  amp  id  join            in your controller  Topic not in forums  forums

User · Answer

You can use sql in your conditions   Topic find  all   conditions   gt     forum id NOT IN        forums map  amp  id

[ruby-on-rails] How to express a NOT IN query with ActiveRecord/Rails?

Examples related to ruby-on-rails

Examples related to rails-activerecord