How to generate an AR query that returns record A, excluding any that are attached to record B of a certain type

My relevant DB tables are an Invoices table and an Advances table with a has_many relationship (an invoice has many advances). I am trying to generate an ActiveRecord query that will return all invoices EXCEPT those that are attached to any advances of advance_type: :pre_settlement.

My current query:

  @invoices = invoices.left_joins(:advances).
      where.not('advances.advance_type': :pre_settlement).
      distinct.
      order(period_ends_at: :desc, created_at: :desc) 

This produces the following SQL:

SELECT "invoices".* FROM "invoices" LEFT OUTER JOIN "advances" ON "advances"."invoice_id" = "invoices"."id" WHERE "advances"."advance_type" != $1  [["advance_type", "pre_settlement"]]

The problem is that in situations where the invoice has multiple advances, as long at least one advance is advance_type != :pre_settlement that invoice will be included. I want to make sure that any invoices that are attached to even one pre_settlement advance will be excluded.

Answer

A general solution to this would be something like this:

class Post < ApplicationRecord
  has_many :comments
  scope :has_flagged_comments, -> { joins(:comments).merge(Comment.flagged) }
end

class Comment < ApplicationRecord
  belongs_to :post
  scope :flagged, -> { where(status: "flagged") }
end

# Posts that don't attract bad behavior:
Post.where.not(id: Post.has_flagged_comments)

I may have some of my syntax off as I don’t have a test dataset to run this on, but this is the general approach. This would generate a query something like this:

select … from posts
where id not in (
 select id from posts
 left join comments on comments.post_id = posts.id
 where status = "flagged"
)