Fetching ancestor/sibling nodes per locale in ActiveRecord
Tags: ruby, rails
Published: 2020-04-23 14:37:47


There are times when we need to work with `parent` records
within the 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).

```ruby
pizza_recipe_page = Page.last

recipes_page = pizza_recipe_page.parent
```

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

```ruby
pages_for_breadcrumbs = page.self_and_parent_nodes
recommended_pages = page.siblings
```

For straightforward cases, we may opt out to use a gem such as
[ancestry](https://github.com/stefankroes/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.

```ruby
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).

| Pages | Field |
| --- | --- |
| parent_id_es | Parent page for spanish locale |
| parent_id_en | Parent page for english locale |
| parent_id_it | Parent page for italian locale |

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

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

PostgreSQL [recursive queries](https://www.postgresqltutorial.com/postgresql-recursive-query/) for the rescue!

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

```ruby
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.

```ruby
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')
```

This post has also been published at [ramblingcode](https://ramblingcode.dev/post/ancestor_chain_per_locale_in_active_record_models/)

That's it for today! If you see an issue or have any question
please give [me](https://www.sahilgadimbayli.com/connect) a shout.