PostgreSQL/Rails 2中多态表A多路径关联查询D表id实现方案

完全可以通过UNION实现该需求，以下是两种场景的具体写法：

PostgreSQL 原生SQL #

注：默认A表带多态标准字段polymorphic_type，用于标识polymorphic_id关联的表类型，值对应为B/C/D

SELECT d_id FROM (
  -- 路径1：A直接关联D
  SELECT a.polymorphic_id AS d_id
  FROM a
  WHERE a.polymorphic_type = 'D'

  UNION

  -- 路径2：A关联B再关联D
  SELECT b.d_id AS d_id
  FROM a
  JOIN b ON a.polymorphic_id = b.id
  WHERE a.polymorphic_type = 'B'

  UNION

  -- 路径3：A关联C关联B再关联D
  SELECT b.d_id AS d_id
  FROM a
  JOIN c ON a.polymorphic_id = c.id
  JOIN b ON c.b_id = b.id
  WHERE a.polymorphic_type = 'C'
) AS all_d_ids;

如果不需要去重，可以把UNION替换为UNION ALL获得更高的查询性能。

Rails 2 框架实现 #

第一步：先配置模型关联 #

# app/models/a.rb
class A < ActiveRecord::Base
  belongs_to :polymorphic, :polymorphic => true
end

# app/models/b.rb
class B < ActiveRecord::Base
  belongs_to :d
  has_many :as, :as => :polymorphic
end

# app/models/c.rb
class C < ActiveRecord::Base
  belongs_to :b
  has_many :as, :as => :polymorphic
  delegate :d, :to => :b
end

# app/models/d.rb
class D < ActiveRecord::Base
  has_many :bs
  has_many :as, :as => :polymorphic
end

第二步：查询实现 #

方式1：纯ActiveRecord写法（适合小数据量场景）

# 分别查询三条路径的D表ID后合并去重
d_ids_direct = A.all(:conditions => {:polymorphic_type => 'D'}).map(&:polymorphic_id)
d_ids_from_b = A.all(:conditions => {:polymorphic_type => 'B'}, :include => {:polymorphic => :d}).map { |a| a.polymorphic.d_id }
d_ids_from_c = A.all(:conditions => {:polymorphic_type => 'C'}, :include => {:polymorphic => {:b => :d}}).map { |a| a.polymorphic.b.d_id }

# 最终去重后的所有D表ID
all_d_ids = (d_ids_direct + d_ids_from_b + d_ids_from_c).uniq
# 要获取D表记录直接用 D.find(all_d_ids) 即可

方式2：直接执行SQL（适合大数据量高性能场景）

sql = <<-EOF
SELECT DISTINCT id FROM d WHERE id IN (
  SELECT polymorphic_id FROM a WHERE polymorphic_type = 'D'
  UNION
  SELECT b.d_id FROM a JOIN b ON a.polymorphic_id = b.id WHERE a.polymorphic_type = 'B'
  UNION
  SELECT b.d_id FROM a JOIN c ON a.polymorphic_id = c.id JOIN b ON c.b_id = b.id WHERE a.polymorphic_type = 'C'
)
EOF
d_records = D.find_by_sql(sql)

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