Rails多态模型BankFacility按关联facility公共属性过滤报错解决

我有一个多态模型BankFacility，代码如下：

class BankFacility < ApplicationRecord
  belongs_to :facility_group
  belongs_to :facility, polymorphic: true
end

该模型关联多种类型的facility，数据示例如下：

<ActiveRecord::Relation [#<BankFacility id: 2, facility_group_id: 2, facility_id: 8, facility_type: "Building", created_at: "2022-07-29 19:54:28", updated_at: "2022-07-29 19:54:28">, #<BankFacility id: 3, facility_group_id: 2, facility_id: 9, facility_type: "Building", created_at: "2022-07-29 19:54:47", updated_at: "2022-07-29 19:54:47">, #<BankFacility id: 4, facility_group_id: 3, facility_id: 1, facility_type: "Vehicle", created_at: "2022-07-30 18:21:49", updated_at: "2022-07-30 18:21:49">, #<BankFacility id: 5, facility_group_id: 4, facility_id: 1, facility_type: "EmployeeCommute", created_at: "2022-07-30 18:30:16", updated_at: "2022-07-30 18:30:16">, #<BankFacility id: 6, facility_group_id: 5, facility_id: 1, facility_type: "BusinessTravel", created_at: "2022-07-30 18:53:22", updated_at: "2022-07-30 18:53:22">, #<BankFacility id: 7, facility_group_id: 6, facility_id: 1, facility_type: "ElectricityGenerator", created_at: "2022-07-30 18:54:34", updated_at: "2022-07-30 18:54:34">]> 

我希望按所有facility共有的date属性进行过滤，尝试执行查询：

BankFacility.where('facility.date >= 2022-07-27')

但始终报错：

ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "facility")
LINE 1: ..."bank_facilities".* FROM "bank_facilities" WHERE (facility.d...

多态关联无法直接通过facility.date查询，因为数据库无法确定要关联哪张具体的facility表，以下是两种可行的实现方式：

方法1：动态生成UNION查询（推荐，适配新增的facility类型） #

先收集所有已存在的facility类型，针对每个类型生成关联查询，最后通过UNION合并结果：

# 获取所有已存在的facility类型
facility_types = BankFacility.pluck(:facility_type).uniq

# 为每个类型构建查询语句
queries = facility_types.map do |type|
  table = type.constantize.table_name
  <<~SQL
    SELECT bf.* FROM bank_facilities bf
    JOIN #{table} f ON bf.facility_id = f.id AND bf.facility_type = '#{type}'
    WHERE f.date >= '2022-07-27'
  SQL
end

# 执行UNION查询并转换为ActiveRecord对象
BankFacility.from("(#{queries.join(' UNION ')}) AS bank_facilities")

方法2：多表LEFT JOIN+条件判断 #

如果facility类型固定，可以手动关联所有对应的表，再通过OR条件匹配对应表的date字段：

BankFacility
  .left_joins("LEFT JOIN buildings b ON bank_facilities.facility_id = b.id AND bank_facilities.facility_type = 'Building'")
  .left_joins("LEFT JOIN vehicles v ON bank_facilities.facility_id = v.id AND bank_facilities.facility_type = 'Vehicle'")
  .left_joins("LEFT JOIN employee_commutes ec ON bank_facilities.facility_id = ec.id AND bank_facilities.facility_type = 'EmployeeCommute'")
  .left_joins("LEFT JOIN business_travels bt ON bank_facilities.facility_id = bt.id AND bank_facilities.facility_type = 'BusinessTravel'")
  .left_joins("LEFT JOIN electricity_generators eg ON bank_facilities.facility_id = eg.id AND bank_facilities.facility_type = 'ElectricityGenerator'")
  .where(
    "b.date >= '2022-07-27' OR v.date >= '2022-07-27' OR ec.date >= '2022-07-27' OR bt.date >= '2022-07-27' OR eg.date >= '2022-07-27'"
  )

注意：新增facility类型时需要手动更新该查询语句。

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