activerecord equivalent to SQL 'minus'

Question

What's the rails way to subtract a query result from another? A database specific SQL example would be:

SELECT Date FROM Store_Information
MINUS
SELECT Date FROM Internet_Sales 

Answer 1

I'll throw this into the mix - not a solution, but might help with the progress:

Best I can think of is to use NOT IN:

StoreInformation.where('date NOT IN (?)', InternetSale.all)

That's Rails 3 - Rails 2 would be:

StoreInformation.all(:conditions => ['date NOT IN(?)', InternetSale.all])

But both of these will first select everything from internet_sales; what you really want is a nested query to do the whole thing in the database engine. For this, I think you'll have to break into find_by_sql and just give a nested query.

Obviously this assumes you're using MySQL! HTH.