Search code examples
sqlruby-on-railswhere-clause

How can I check for an empty association with a where statement in rails?


I have a model called Category that can have many subcategories (which are also Category records). I want to collect all Categories in the database that have no subcategories with something like:

Category.where(subcategories: [])

But every intuitive [to me] variation of this that I've tried either throws an error or returns an empty set. It's generating some SQL that I don't quite understand:

Category Load (0.5ms) SELECT "categories".* FROM "categories" WHERE 1=0
=> #<ActiveRecord::Relation []> 

Can anyone tell me the proper way to do this?

EDIT: This is the way the subcategories relationship is defined

class Category < ActiveRecord::Base
    has_many :subcategories, class_name: 'Category', foreign_key: 'parent_id'
    belongs_to :parent, class_name: 'Category'
end

Solution

  • OK, solved. I just needed to tweak Sharvy's suggestion (one big thing I forgot is to add a "joins").

    The following worked:

    Category.includes(:subcategories).where(subcategories: nil)
    

    UPDATE 2: OK, this time I really got it working (pretty sure, at least...). I had to use some ugly SQL rather than just some pretty ActiveRecord syntax, but the following seems to do what I want:

    Category.joins("LEFT JOIN categories AS subcategories ON subcategories.parent_id = categories.id").where("subcategories IS NULL")
    

    UPDATE 1: Sorry, nevermind, this doesn't work. As I commented below, I jumped the gun because I saw that this query was returning the proper category count for both where(... nil) and where.not(... nil), but it wasn't actually returning the proper categories. It is returning all the categories that do not have a parent category rather than all the categories that do not have any subcategories.

    Here is some sample output from this query:

    Category.includes(:subcategories).where(subcategories: nil).last
      Category Load (0.7ms)  SELECT  "categories".* FROM "categories" WHERE "categories"."parent_id" IS NULL  ORDER BY "categories"."id" DESC LIMIT 1
      Category Load (0.5ms)  SELECT "categories".* FROM "categories" WHERE "categories"."parent_id" IN (3158)
     => #<Category id: 3158, name: "A parent", parent_id: nil, created_at: "2015-03-23 19:18:40", updated_at: "2015-03-23 19:18:40", operator_id: nil>