How to encode "where not exists" queries?

[nello]

Hi, I've been poking at this for over a day now and have only really served to illuminate just how little I understand the Sequel toolkit.

I'm trying to code up something pretty simple in SQL. I've got a table called direct_debits that has some obvious fields: loan_id, status, amount, due_date and processed_date (there are others, but trying to simplify here). I'm attempting to display rejected debits unless there has been a subsequent resubmission of the amount.

My current query shows all the rejected debits except for any that have the same due debit in a different state with a later processed date:

select *
from direct_debits d1
where status = 'rejected' and not exists (
    select * from direct_debits d2
    where d1.loan_id = d2.loan_id
    and d1.due_date = d2.due_date
    and d1.amount = d2.amount
    and d2.status <> 'rejected'
    and d2.processed_date > d1.processed_date
)

Am I right in thinking this is just not possible in Sequel? I've tried so many things my head just hurts. I get that I can do a better job of tracking that the resubmitted debit is actually this debit, but that's totally not my problem here. I'm just finding the toolkit pretty much impenetrable.

The best I've got so far is something like this (but I know it's wrong and every little variation of it just explodes, probably including this one):

          rejected_debits = Models::DirectDebit
            .where(Sequel.qualify(:direct_debits, :status) => 'rejected')

          rejected_debits.exclude((
             Models::DirectDebit.join(rejected_debits, loan_id: Sequel.qualify(:direct_debits, :loan_id))
                .where {Sequel.qualify(:direct_debits, :status) != 'rejected' }
                .where(Sequel.qualify(:direct_debits, :amount) => Sequel.qualify(rejected_debits, :amount))
                .where(Sequel.qualify(:direct_debits, :due_date) => Sequel.qualify(rejected_debits, :due_date))
                .where{Sequel.qualify(:direct_debits, :processed_date) > Sequel.qualify(rejected_fees, :processed_date) }
            ).exists)
            

I've tried recasting it as a cte expression, but that ended up just returning every row no matter what I varied. I've even asked ChatGPT but it hallucinated something mental.

Help!

[tycooon]

You can try smth like this for example:

exists_case =
  DB.from(Sequel[:direct_debits].as(:d2))
    .where(Sequel[:d2][:loan_id] =~ Sequel[:d1][:loan_id])
    # more conditions here
    .exists

 DB.from(Sequel[:direct_debits].as(:d1)).where(status: "rejected").exclude(exists_case)

[nello]

Thanks Yuri, I'll try that out and let you know how I get on. I really appreciate the hand!