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

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.

CodePudding user response：

I think the following might work for you:

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

CodePudding user response：

Rails 7 has this built in

Employee.where.associated(:documents)

CodePudding user response：

The questions is pretty confusing but if you have a list of documents and want to find employees with matches with all or some of the records you can do it by grouping and using having to set conditions on the group:

Employee
  .left_joins(:documents)
  .where(documents: { id: documents })
  .group(:id)
  # HAVING COUNT("documents"."id") < 7
  .having(Document.arel_table[:id].count.lt(documents.size))

This would give users that don't have matches with all the documents but may have some. The conditions can be simply changed by using eq, lteq, gt or gteq instead of lt (less than).

On Postgres you can use COUNT(documents.*) instead with:

.having(Document.arel_table[Arel.star].count.lt(documents.size))