How can I structure this Rails / PostgreSQL query to return model instances based on an attribute for model children?

Question

I am working on an application where User has many projects and Project many roles. Role has a boolean filled attribute indicating when someone took this role.

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

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

The problem is that part of roles.filled IS TRUE request corresponds to projects with a mixture of filled and unfilled roles. I need this to fit projects that fill all the roles.

Finding PostgreSQL documentation looks like bool_and is probably the way to go, but my SQL skills are small and I'm glad I wasn’t able to get it working yet.

I understand that I could do this easily with select or reject , but I would like to keep it efficient by simply querying the database.

Can someone offer some advice please?

Update: models (simplified) and their relationships:

application / models / user.rb

 class User < ActiveRecord::Base has_many :projects end 

application / models / project.rb

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

application / 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 should resort to an aggregated function , since this condition applies to a combination of many records (all roles.filled fields must be true), Thus, you group all roles using projects.id and check if every(roles.filled) :

Here is a suggestion:

 @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 : here's the SQL logic:

for each project, I will check if it has NO role.id or ONLY roles. The reason you cannot use the where clause for roles.id IS TRUE is because it will select only these, before grouping and filtering in the having and, therefore, excluding the roles.filled strings. In other words, this would be equivalent to doing roles.id is NULL AND Roles.filled IS TRUE