How to find model records having some or no associated model records in rails?

Question

I am creating an Employee Management System app using Rails 7. Here I have one Employee model, one Document model having a list of all documents and these two are associated through the EmployeeDocuments model which has Employee ID and Document ID. Now I want to use an action mailer to send a list of employees with documents they have not submitted. The problem is I cannot get the Employee list with some or no documents submitted in a single query. I can get Employees with no submitted documents like this:

Employee.includes(:documents).where(documents: {id: nil})

and Employees with some or all documents submitted by:

Employee.includes(:documents).where.not(documents: {id: nil})

I want the list so that I can iterate through their missing documents by:

Document.where.not(id: employee.documents.pluck(:id))

and send the list. Currently, my mailer looks like this:

class DocsNotifierMailer < ApplicationMailer
  default from: '[email protected]'

  def notification_email 
    @employees = Employee.all      
    mail(to: '[email protected]', 
      subject: 'Reminder for missing documents')   
  end 
end

So I can get the list of all employees in my HTML template and there I am using an If statement like this:

<% if employee.documents.length < Document.all.count %>

to filter out the employees with all documents. But I want to filter them in the mailer itself. Since I am a beginner, can not find a way out of this. Please help.

Answer 1

I think the following might work for you:

Employee
  .left_outer_joins(:documents)
  .group('employees.id')
  .having("COUNT(documents.id) < #{Document.count}")