Writing "not in" sql query using AREL

Question

I have a scope defined as follows:

scope :ignore_unavailable, lambda { 
  where([ "Item.id NOT IN (SELECT id FROM Cars WHERE Cars.status = 'NA'" ])
}

Currently its using hardcoded tables names. How can I improve it using frameworks like Arel ? Will appreciate any help here.

I am on Rails 3.2

Valleau

Answer 1

If you are using rails 4, one way would be

scope :ignore_unavailable, lambda {
  where.not(id: Car.where(:status => "NA").pluck(:id))
}

For rails 3

scope :ignore_unavailable, lambda {
  where("id not in (?)", Car.where(:status => "NA").pluck(:id))
}

Patrinapatriot

unfortunately I am on rails 3.2 – Valleau

Updated my answer for rails 3 – Patrinapatriot

Thanks. This is great! One question though, Wont we be executing two SQL queries here instead of one like in original case ? – Valleau

subqueries are actually bad. Performance wise having two queries might be better than having the subquery in your case. Read this blog, percona.com/blog/2010/10/25/mysql-limitations-part-3-subqueries – Patrinapatriot

@Patrinapatriot Subqueries are not “bad”. Loading an array of potentially millions of items out of laziness is “bad”. – Muddy

An uncorrelated subquery, such as presented in the original question, should end up being nothing more than two queries executed sequentially, so which implementation is faster depends on the dynamic buffer management efficiency of the database vs rails/ruby. – Terbia

Answer 2

Since the task description asks for an answer using AREL, I present following:

class Car
  scope :available, -> { where(arel_table[:status].not_in(['NA'])) }
end

class Item
  scope :available, -> { where(:id => Car.available) }
end

The sql should be something like the following:

SELECT [items].*
FROM [items]
WHERE [item].[id] IN (
    SELECT [cars].[id]
    FROM [cars]
    WHERE [car].[status] NOT IN ('NA')
  )

Obviously, rails 4 has the not scope, so this is a solution for rails 3.

The above code has two benefits:

• It performs a single query
• The table columns are correctly namespaced (unlike when using raw sql)

Bataan

Thanks a lot ! I had problems with namespaces in a complex query, only Arel could help me here. :) – Tradition