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).
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.
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 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).
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 for the rescue!
This assumes you are using Active Record. 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')
This post has also been published at ramblingcode
That's it for today! If you see an issue or have any question
please give me a shout.
Top comments (2)
Recursive programming is the worst programming style. Don't do that. Use pre-balanced trees.
Thanks for reading!
Care to elaborate pre balanced trees? Would love to know about it.
There isn't really an evil for all cases style, each has its own use case, same with recursion(extensively used in elixir, for instance).