在WHERE查询中使用自定义方法：查询拥有余额的客户

首先得明确一个核心问题：你现在定义的balance是Ruby实例方法，是在应用内存里计算的，数据库根本不知道这个方法的存在，所以没法直接放到WHERE子句里使用。要实现这个查询，我们得把Ruby方法里的计算逻辑翻译成数据库能理解的SQL才行。

先拆解你的业务逻辑：
你的balance是「客户所有订单的total总和」减去「所有付款的总和」，而订单的total又分两种情况——有大于0的折扣时是weekly_cost * weeks * discount/100，没折扣就是weekly_cost * weeks。

下面是具体的实现步骤：

1. 把Purchase#total的逻辑转成SQL表达式 #

对应SQL里的CASE语句，用来处理折扣的分支判断：

CASE
  WHEN purchases.discount > 0 THEN (purchases.weekly_cost * purchases.weeks) * (purchases.discount / 100.0)
  ELSE purchases.weekly_cost * purchases.weeks
END

2. 构建查询获取有余额的客户 #

我们需要用ActiveRecord的joins关联相关表，然后用SUM做聚合计算，最后用having来筛选余额不为0的客户（因为WHERE不能直接使用聚合函数，得用having配合分组）：

Client.joins(:purchases, :payments)
      .select(
        'clients.*',
        '(SUM(CASE WHEN purchases.discount > 0 THEN (purchases.weekly_cost * purchases.weeks) * (purchases.discount / 100.0) ELSE purchases.weekly_cost * purchases.weeks END) - SUM(payments.amount)) AS balance'
      )
      .group('clients.id')
      .having(
        '(SUM(CASE WHEN purchases.discount > 0 THEN (purchases.weekly_cost * purchases.weeks) * (purchases.discount / 100.0) ELSE purchases.weekly_cost * purchases.weeks END) - SUM(payments.amount)) != 0'
      )

3. 优化代码可读性（可选） #

上面的SQL表达式太长，重复写两次很麻烦，我们可以用Arel（ActiveRecord的底层SQL构建工具）来重构查询，让代码更简洁易维护：

class Client < ActiveRecord::Base
  has_many :purchases
  has_many :payments

  def self.with_balance
    # 用Arel构建订单total的计算逻辑
    purchase_table = Purchase.arel_table
    payment_table = Payment.arel_table

    base_total = purchase_table[:weekly_cost] * purchase_table[:weeks]
    discounted_total = base_total * (purchase_table[:discount] / 100.0)
    total_case = Arel::Nodes::Case.new
                  .when(purchase_table[:discount].gt(0))
                  .then(discounted_total)
                  .else(base_total)

    # 计算balance
    balance = total_case.sum - payment_table[:amount].sum

    # 构建最终查询
    joins(:purchases, :payments)
      .select('clients.*', balance.as('balance'))
      .group('clients.id')
      .having(balance.neq(0))
  end
end

之后你直接调用Client.with_balance就能拿到所有有余额的客户了，每个客户对象还会有一个balance属性可以直接调用。

额外注意点 #

• 如果有些客户没有任何订单或者付款记录，joins会把这些客户排除在外。要是想包含这些客户，把joins换成left_joins，同时把聚合函数改成COALESCE(SUM(...), 0)，避免因为NULL导致计算错误，比如把SUM(payments.amount)改成COALESCE(SUM(payments.amount), 0)。
• 确保数据库里的discount字段是数值类型（比如integer或decimal），不然除法计算可能会出问题。

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