Equivalent for SQL WITH RECURSIVE in Arel

Question

I have the following SQL which recursively gets all of the parents of a record using WITH RECURSIVE. What would the equivalent of this be in Arel?

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)

Answer 1

After taking a look at the test that @cschroed pointed me to I was able to refactor my raw SQL into:

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