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
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>