treeexample.md

nodes
id   code              depth  parent
1    B                 0      {NULL}
2    B-123             1      1
3    B-123-BLAH        2      2
...
6    B-123-BLAH-1-2-3  6      5

The codes are guaranteed not to overlap, so you can use righthanded wildcards to walk upward. I have a node at depth 3 (or deeper), and I want the depth 0 node, which is easy:

select * from nodes where depth = 0 and 'B-123-BLAH-ARGLEBARGLE' like code || '?' limit 1;

---

Now I want to warm a cache, so I have 1000 level 3 (and 4, and 6, etc) nodes, and I want all the level 0s. My preferred output would be descendent.code, ancestor.* in each tuple, for all input rows.

I am not smart enough to write this SQL.

def self.ancestors(level)
  match = select("transform(code) || %")
  Node.where(level: level)
    .where('code LIKE ANY array[?]', match)
end

scope :ancestors_of, -> (level, children) do
  match = children.select("transform(code) || %")
  where(level: level)
    .where('code LIKE ANY array[?]', match)
end

match = children.select("regex_replace(code, repeat('-[^-]*', depth - #{level}), '') || %")