gpt4 book ai didi

sql - Arel 中的 SQL WITH RECURSIVE 等价物

转载 作者:数据小太阳 更新时间:2023-10-29 07:31:52 25 4
gpt4 key购买 nike

我有以下 SQL,它使用 WITH RECURSIVE 递归获取记录的所有父项。在阿雷尔,这相当于什么?

table_name = self.class.table_name
arel_table = self.class.arel_table

sql = <<-SQL
WITH RECURSIVE a AS (
SELECT * FROM #{table_name} WHERE id = '#{id}'
UNION ALL
SELECT b.* FROM #{table_name} b
JOIN a ON b.id = a.parent_id
) SELECT id FROM a
SQL

ids = ActiveRecord::Base.connection.execute(sql).field_values('id')
ordering = ids.map { |id| arel_table[:id].eq(id) }
self.class.where(id: ids).order(ordering)

最佳答案

在查看了@cschroed 向我指出的测试后,我能够将我的原始 SQL 重构为:

def lineage
hierarchy = Arel::Table.new :hierarchy
recursive_table = Arel::Table.new(table_name).alias :recursive
select_manager = Arel::SelectManager.new(ActiveRecord::Base).freeze

non_recursive_term = select_manager.dup.tap do |m|
m.from table_name
m.project Arel.star
m.where arel_table[:id].eq(id)
end

recursive_term = select_manager.dup.tap do |m|
m.from recursive_table
m.project recursive_table[Arel.star]
m.join hierarchy
m.on recursive_table[:id].eq(hierarchy[:parent_id])
end

union = non_recursive_term.union :all, recursive_term
as_statement = Arel::Nodes::As.new hierarchy, union

manager = select_manager.dup.tap do |m|
m.with :recursive, as_statement
m.from hierarchy
m.project hierarchy[:id]
end

ids = ActiveRecord::Base.connection.execute(manager.to_sql).field_values('id')
ordering = ids.map { |id| arel_table[:id].eq(id) }

self.class.where(id: ids).order(ordering)
end

private

def arel_table
self.class.arel_table
end

def table_name
self.class.table_name
end

关于sql - Arel 中的 SQL WITH RECURSIVE 等价物,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/30899226/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com