ActiveRecord如何根据子模型Invoice付款状态筛选Purchase记录

你之前的写法只能筛选出「至少有一张已付款发票」的采购，会同时包含全部已付和部分已付的记录，无法区分三类状态，以下是三种场景的具体实现方案：

逻辑为排除所有存在未付款发票的采购：

Purchase.left_joins(:invoices)
        .where(invoices: { paid: [false, nil] })
        .group('purchases.id')
        .having('COUNT(invoices.id) = 0')

# 若需要排除没有任何关联发票的采购，增加存在判断即可
Purchase.left_joins(:invoices)
        .where(Invoice.where('invoices.purchase_id = purchases.id').arel.exists)
        .where(invoices: { paid: [false, nil] })
        .group('purchases.id')
        .having('COUNT(invoices.id) = 0')

逻辑为统计关联发票中至少有1张已付、至少有1张未付：

Purchase.joins(:invoices)
        .group('purchases.id')
        .having('COUNT(CASE WHEN invoices.paid = true THEN 1 END) > 0')
        .having('COUNT(CASE WHEN invoices.paid = false THEN 1 END) > 0')

逻辑为排除所有存在已付款发票的采购：

Purchase.left_joins(:invoices)
        .where(invoices: { paid: true })
        .group('purchases.id')
        .having('COUNT(invoices.id) = 0')

# 若需要排除没有任何关联发票的采购，同样增加存在判断即可

可以把上述逻辑直接封装到Purchase模型中，后续直接调用即可：

class Purchase < ApplicationRecord
  has_many :invoices

  # 全部已付
  scope :fully_paid, -> {
    left_joins(:invoices)
      .where(invoices: { paid: [false, nil] })
      .group('purchases.id')
      .having('COUNT(invoices.id) = 0')
  }

  # 部分已付
  scope :partially_paid, -> {
    joins(:invoices)
      .group('purchases.id')
      .having('COUNT(CASE WHEN invoices.paid = true THEN 1 END) > 0')
      .having('COUNT(CASE WHEN invoices.paid = false THEN 1 END) > 0')
  }

  # 未付款
  scope :unpaid, -> {
    left_joins(:invoices)
      .where(invoices: { paid: true })
      .group('purchases.id')
      .having('COUNT(invoices.id) = 0')
  }
end

调用示例：

• 获取全部已付采购：Purchase.fully_paid
• 获取部分已付采购：Purchase.partially_paid
• 获取未付款采购：Purchase.unpaid

内容的提问来源于stack exchange，提问作者esmanbo