How can I structure this Rails/PostgreSQL query to return instances of a model based on an attribute on the model's children?

Question

I'm working on an app where a User has many projects and a Project has many roles. Role has a boolean filled attribute to indicate when someone has taken the role on.

I'd like to build a query which returns all projects that either have no roles or have roles which have all been filled. This is the closest I've got so far:

@user.projects.includes(:roles).where("roles.id IS NULL OR roles.filled IS TRUE").references(:roles)

The problem is that the roles.filled IS TRUE part of the query is matching projects with a mixture of filled and unfilled roles. I need this to only match projects on which all roles are filled.

Searching the PostgreSQL documentation, it looks like bool_and is probably the way to go but my SQL skills aren't great and I haven't managed to get it working yet.

I realise I could do this easily with select or reject but I'd like to keep it efficient by just querying the database.

Can anyone offer some advice please?

Update: The (simplified) models and their relationships:

app/models/user.rb

class User < ActiveRecord::Base
  has_many :projects
end

app/models/project.rb

class Project < ActiveRecord::Base
  belongs_to :user
  has_many :roles
end

app/models/role.rb

class Role < ActiveRecord::Base
  belongs_to :project

  # `filled` is a boolean attribute with a default value of false
end

Answer 1

You must resort to an aggregate function since the condition applies to the combination of many records (all fields of roles.filled must be true). So you group all roles by projects.id and check if every(roles.filled) is true:

Here's a proposal :

@user.projects.
      joins("left outer join roles on roles.project_id = projects.id").
      group("projects.id").
      having("every(roles.filled IS TRUE) OR every(roles.id IS NULL)")
      select("projects.*")

Update : Ok here's the logic behind this SQL :

for every project, I'll check if it has NO roles.id OR ONLY roles.filled. The reason you cannot use the where clause for roles.id IS TRUEis that it would only select those, before grouping and filtering in the having clause and therefore excluding the roles.filledrows. In other words it would be the equivalent of doing roles.id is NULL AND Roles.filled IS TRUE