Rails: ActiveRecord scope based on property of child

Question

I have three models Component, Version and User which are associated with each other like so:

class Component < ActiveRecord::Base
    has_many :versions, class_name 'ComponentVersion'
end

class ComponentVersion < ActiveRecord::Base
    belongs_to :component
    belongs_to :approver, class_name: 'User'
end

class User < ActiveRecord::Base
end

I want to create two scopes on Component: approved and unapproved. A Component is 'approved' when it has at least one approved version, and a version is approved when its approver_id is not nil. So Component.approved should return all approved components and Component.unapproved should return all unapproved versions.

Unfortunately, I have no idea how to do this.

I can define scopes on ComponentVersion easily enough:

class ComponentVersion < ActiveRecord::Base
    belongs_to :component
    belongs_to :approver, class_name: 'User'
    scope :approved,   -> { where('approver_id IS NOT NULL') }
    scope :unapproved, -> { where('approver_id IS NULL') }
end 

Then at the console Component.joins(:version).merge(ComponentVersion.approved) gives me something close to what I'd want for Component.approved, except it includes duplicates (if a Component has more than one approved version, it gets returned more than once.)

And Component.joins(:version).merge(ComponentVersion.unapproved) is no good for the opposite because it returns Components which have both unapproved and approved versions, when I only want Components which only have unapproved Versions (or no Versions at all).

I've also tried passing SQL queries to joins that include OUTER JOIN instead of INNER but they're not giving me what I want, and they're stretching my limited understanding of SQL.

How can I get the queries I need?

(I'm using Postgres and Rails 3.2.13, in case it matters.)

Answer 1

It seems impossible to get what you want using query generator DSL. I usually use correlated subqueries for this.

class Component
  scope :approved, lambda{ where('EXISTS (SELECT 1 FROM component_versions WHERE component_versions.component_id = components.id AND component_versions.approver_id IS NOT NULL)') }
end

Another version

class Component
  scope :approved, lambda { where('id IN (SELECT c.id FROM components c JOIN component_versions cv ON c.id = cv.component_id AND cv.approver_id IS NOT NULL)' }
end

With Postgres it probably doesn't matter which one you'll use, it has a pretty good query planner.