Geek Answers Handbook

Not in a sql query using AREL

I have an area defined as follows:

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

It currently uses hard-coded table names. How can I improve it with frameworks like Arel? Understand any help here.

I'm on Rails 3.2

+4
source share
2 answers

If you use 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))
}
+4
source

Since the task description requests a response using AREL, I present the following:

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

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

In sql, there 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 have a region not, so this is a solution for rails 3.

The above code has two advantages:

  • ( raw sql)
+6

More articles:


All Articles