8

Is there a way to use EXISTS with ActiveRecord besides find_by_sql?

I'd like a nice way to find all records without an association in a One-to-Many relationship.

SELECT DISTINCT store_type FROM stores
  WHERE NOT EXISTS (SELECT * FROM cities_stores
                    WHERE cities_stores.store_type = stores.store_type)
jspooner
  • 10,975
  • 11
  • 58
  • 81

1 Answers1

6
Store.all(:select => "DISTINCT store_type",
          :conditions => "NOT EXISTS (SELECT * FROM cities_stores WHERE cities_stores.store_type = stores.store_type)")

ActiveRecord will execute the same query as what you entered above. The returned Store instances will have a single store_type attribute.

François Beausoleil
  • 16,265
  • 11
  • 67
  • 90
  • 7
    It would be nice if there were a way to write the inner query using standard ActiveRecord methods, rather than raw SQL. Is that possible? – Diogenes Creosote Jul 04 '13 at 04:28
  • @AndrewCone did you try using `join`? See official document http://guides.rubyonrails.org/active_record_querying.html#using-array-hash-of-named-associations – zhongxiao37 Jul 08 '15 at 09:18