pending_invoices.rb

class Merchant < ApplicationRecord
  has_many :items
  has_many :invoices
  has_many :invoice_items, through: :invoices
  has_many :transactions, through: :invoices
  has_many :customers, through: :invoices

  def customers_with_pending_invoices
    Customer.find_by_sql("select customers.* from customers
      join invoices
      on customers.id = invoices.customer_id
      where invoices.id in (
        select invoices.id
        from invoices
        join transactions
        on invoices.id = transactions.invoice_id
        where transactions.result = 'failed'
        and invoices.merchant_id = #{self.id}
        except
        select invoices.id
        from invoices
        join transactions
        on invoices.id = transactions.invoice_id
        where transactions.result = 'success'
        and invoices.merchant_id = #{self.id}
      )
      and invoices.merchant_id = #{self.id};")
  end
end