Mysql2 错误 - 使用 COALESCE，where 子句中的未知列

Question

在我的 Rails 4.1 应用程序中，我正在构建一个查询，以通过多态关系(买方)的属性(名称)在我的集合(订单)中查找记录。该多态有 2 个可能的表，因此我使用 2 个 LEFT JOINS + COALESCE 来合并属性。然后尝试在 where 子句中使用 COALESCE 中的值。

我的 ActiveRecord 模型中的方法如下所示:

class Order
  ...
  scope :join_by_buyer_name, -> { select("stock_orders.*", "COALESCE(core_customers.business_name, core_entities.name) AS buyer_name")
      .joins("LEFT JOIN core_customers ON stock_orders.buyer_id = core_customers.id AND stock_orders.buyer_type='Core::Customer'")
      .joins("LEFT JOIN core_entities ON stock_orders.buyer_id = core_entities.id AND stock_orders.buyer_type='Core::Entity'")
    }
  ...

  def find_by_buyer_name(term)
    all.join_by_buyer_name.where("buyer_name LIKE ?", term)
  end


end

当执行 ActiveRecord 查询时，它看起来像这样

SELECT stock_orders.*, COALESCE(core_customers.business_name, core_entities.name) AS buyer_name
FROM `stock_orders`
LEFT JOIN core_customers ON stock_orders.buyer_id = core_customers.id AND stock_orders.buyer_type='Core::Customer'
LEFT JOIN core_entities ON stock_orders.buyer_id = core_entities.id AND stock_orders.buyer_type='Core::Entity'
WHERE `stock_orders`.`type` IN ('Stock::SalesOrder') AND (buyer_name LIKE "blah")

直到最近这才起作用，但是我开始收到以下错误:

Mysql2::Error:“where 子句”中的未知列“buyer_name”:SELECT stock_orders.*、COALESCE 等等等

它非常适合按 COALESCE 值排序 - 即 collection.order("buyer_name asc") ，直到最近，where 查询也完美地工作......

我不确定发生了什么变化，并且在 Rails 或 Mysql 文档中看不到任何内容。你能看出这里出了什么问题吗？请帮忙!

Answer 1

您不能在从 SELECT 子句创建的 WHERE 子句中使用 ALIAS。请改用计算列，

AND (COALESCE(core_customers.business_name, core_entities.name) LIKE "blah")

如果您想使用ALIAS，您必须将其包装在子查询中，如下面的查询，

SELECT *
FROM
     (
     SELECT stock_orders.*, 
           COALESCE(core_customers.business_name, core_entities.name) AS buyer_name
     FROM `stock_orders`
          LEFT JOIN core_customers 
                ON stock_orders.buyer_id = core_customers.id 
                     AND stock_orders.buyer_type='Core::Customer'
          LEFT JOIN core_entities 
                     ON stock_orders.buyer_id = core_entities.id 
                     AND stock_orders.buyer_type='Core::Entity'
     ) subquery
WHERE `type` IN ('Stock::SalesOrder') 
      AND (buyer_name LIKE "blah")