Fetching ancestors/sibling records per locale in ActiveRecord

(Updated: )
,

Context

There are times when we need to work with parent records within our database.

A practical example we will use today is Pages table with a parent_id column, referencing another Page record.

This will be used in building page breadcrumbs(parents) and fetching recommended pages(siblings).

pizza_recipe_page = Page.last

recipes_page = pizza_recipe_page.parent

This builds up a tree like structure, and we would most probably need a way to fetch the page's parent pages or related pages when working in our application.

pages_for_breadcrumbs = page.self_and_parent_nodes
recommended_pages = page.siblings

Problem

For straightforward cases, we may opt out to use a gem such as ancestry and be done with the task. This will work, assuming you are using one column such as parent_id to set a parent for the record.

class Page
  # assumes your parent record
  # reference is based only on a single column
  has_ancestry
end

However, you would run into a slight inconvenience if you are using multiple columns to set parents, such as for different languages(parent_id_en, parent_id_es, parent_id_it).

PagesField
parent_id_esParent page for spanish locale
parent_id_enParent page for english locale
parent_id_itParent page for italian locale

Now, imagine querying for related pages per locale, and generating breadcrumbs… Not so straightforward!

Solution

Thus, we have to build functionality on our own to enable fetching ancestors/siblings per locale.

PostgreSQL recursive queries for the rescue!

This assumes you are using ActiveRecord. However, you may use the same concept regardless of the ORM.

class Page
  # Starting with the direct parent
  # iterate through the chain, querying
  # parent of the found record as we go higher
  # terminating when nothing is found.

  # Maintains the correct order of results.
  # So, ids will be self_id, parent_node_id,
  # greatparent_node_id, greatgreatparent_node_id...

  # @returns ActiveRecord::Relation
  def self_and_parent_nodes(locale:)
    query = <<~SQL
        WITH RECURSIVE parent_nodes AS (
          SELECT id, parent_id_#{locale}
          FROM #{self.class.table_name}
          WHERE id = #{id}
          UNION
          SELECT c.id, c.parent_id_#{locale}
          FROM #{self.class.table_name} c
            JOIN parent_nodes p ON p.parent_id_#{locale} = c.id
      ) SELECT id FROM parent_nodes;
    SQL

    self_and_parent_node_ids =
      ::ApplicationRecord.connection
                         .execute(query)
                         .map { |record| record.fetch('id') }

    # Fetch AR objects from returned collection of ids

    self.class
        .where(id: self_and_parent_node_ids)
        .ordered_by_ids(self_and_parent_node_ids)
  end

  # Find records that share parent with self
  #
  # @returns ActiveRecord::Relation
  
  def siblings(locale:)
    self.class
        .where("parent_id_#{locale} = ? AND #{self.class.table_name}.id != ?",
               public_send("parent_id_#{locale}"), id)
  end

  # Maintain order of results
  #
  # @returns ActiveRecord::Relation

  def ordered_by_ids(ids)
    order_results_query =
      ids.map.with_index { |id, position| "WHEN #{id} THEN #{position}" }.join(' ')

    order_sql =
      Arel.sql(
        "CASE #{self.table_name}.id #{order_results_query} ELSE #{ids.size} END, #{self.table_name}.id"
      )

    order(order_sql)
  end
end

Now you are able to fetch ancestor and sibling nodes per locale. Go ahead and create/test some records.

page = Page.create!(
  name: 'Test Page',
  parent_id_en: id_of_some_english_page,
  parent_id_es: id_of_some_spanish_page
)

pages_for_breadcrumbs =
  page.self_and_parent_nodes(locale: 'en')

recommended_pages_for_spain =
  page.siblings(locale: 'es')

That’s it for today! If you see an issue or have any question please give me a shout.