How to express a NOT IN query with ActiveRecord/Rails?
Ruby on-RailsRails ActiverecordRuby on-Rails Problem Overview
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 => { :forum_id => @forums.map(&:id) })
which is the same as
SELECT * FROM topics WHERE forum_id IN (<@forum ids>)
I am wondering if there is a way to do NOT IN
with that, like:
SELECT * FROM topics WHERE forum_id NOT IN (<@forum ids>)
Ruby on-Rails Solutions
Solution 1 - Ruby on-Rails
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]
Solution 2 - Ruby on-Rails
FYI, In Rails 4, you can use not
syntax:
Article.where.not(title: ['Rails 3', 'Rails 5'])
Solution 3 - Ruby on-Rails
You can try something like:
Topic.find(:all, :conditions => ['forum_id not in (?)', @forums.map(&:id)])
You might need to do @forums.map(&: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 => ['forum_id not in (?)', forums.select(&:id).join(',')] }
# in your controller
Topic.not_in_forums(@forums)
Solution 4 - Ruby on-Rails
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.
Solution 5 - Ruby on-Rails
To expand on @Trung Lê answer, in Rails 4 you can do the following:
Topic.where.not(forum_id:@forums.map(&: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(&:id))
Rails 4 makes it so much easier!
Solution 6 - Ruby on-Rails
The accepted solution fails if @forums
is empty. To workaround this I had to do
Topic.find(:all, :conditions => ['forum_id not in (?)', (@forums.empty? ? '' : @forums.map(&:id))])
Or, if using Rails 3+:
Topic.where( 'forum_id not in (?)', (@forums.empty? ? '' : @forums.map(&:id)) ).all
Solution 7 - Ruby on-Rails
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 => @forums.map(&:id)}}
Topic.where{forum_id.not_in @forums.map(&:id)}
Topic.where{forum_id << @forums.map(&:id)}
Solution 8 - Ruby on-Rails
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 (<@forum ids>)
...could be expressed like this:
Topic.where(:forum_id.nin => @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.
Solution 9 - Ruby on-Rails
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}])
Solution 10 - Ruby on-Rails
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 => "left join forums on (forums.id = topics.forum_id and some_condition)", :conditions => "forums.id is null")
Which would be more efficient than doing an SQL not in
Solution 11 - Ruby on-Rails
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(&:id)
Solution 12 - Ruby on-Rails
You can use sql in your conditions:
Topic.find(:all, :conditions => [ "forum_id NOT IN (?)", @forums.map(&:id)])
Solution 13 - Ruby on-Rails
Piggybacking off of jonnii:
Topic.find(:all, :conditions => ['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
Solution 14 - Ruby on-Rails
When you query a blank array add "<< 0" to the array in the where block so it doesn't return "NULL" and break the query.
Topic.where('id not in (?)',actions << 0)
If actions could be an empty or blank array.
Solution 15 - Ruby on-Rails
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, ->(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 << (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. << is the not in operator in squeel.
Hope this helps someone.
Solution 16 - Ruby on-Rails
If someone want to use two or more conditions, you can do that:
your_array = [1,2,3,4]
your_string = "SOMETHING"
YourModel.where('variable1 NOT IN (?) AND variable2=(?)',Array.wrap(your_array),your_string)