Rails using PostgreSQL Recursive Query to find ancestors

node.rb

# CREATE TABLE nodes (
#  id bigserial primary key,
#  parent_id bigint null
# )

class Node < ApplicationRecord
  
  def ancestors(depth: 10)
    query = <<- SQL
      WITH RECURSIVE node_paths(#{Node.column_names.join(', ')}, depth) AS (
        SELECT #{column_names.join(', ')}, 1
        FROM #{Node.table_name}
        WHERE #{Node.table_name}.id = #{parent_id}
      UNION ALL
        SELECT #{column_names.join(', ')}, depth + 1
        FROM #{Node.table_name} JOIN node_paths ON node_paths.parent_id = #{Node.table_name}.id
        WHERE depth < #{depth}
      )
      SELECT * FROM node_paths ORDER BY depth
    SQL
    Node.find_by_sql(query)
  end
  
  private
  
  def column_names_for_recursive
    @column_names ||= Node.column_names.map do |name|
      [Node.table_name, name].join('.')
    end
  end
end

# Example
Node.last.ancestors