Using associations to combine hierarchical SQL data

Issues
1

Hi folks :wave:

I’ve got a hierarchical tree of data in a table named categories - each category can have a parent (as noted by the parent_id column, referring to the id column in that same table). I’m also using the PostgreSQL extension ltree and have a path column that keeps track of the branch of categories. And with all of this, what I’m trying to do is have an association that can be used via combine to get the ancestors of each category.

I’ve put together an example script that reproduces the problem: https://gist.github.com/pat/30e29b795e52e380f6922fa0c9572f6e - the SQL statements executed are included in the output for debugging assistance.

The statements of SQL that are being run are correct, and do return the correct records, but those records don’t get added to the entities. I’m very new to the ROM libraries, but from a bit of spelunking I’m wondering if the mapper that’s used to combine the results is involved - I think it’s comparing ids to parent ids to load up the associated data. Given I’m not just returning the direct parent, but also all parents, that’s probably not helpful - is it possible to customise the mapper’s behaviour somehow? But also, given it’s not even returning the direct parent, perhaps my guess here is completely off the mark and it’s something other than the mapper that I should be looking to change.

Any thoughts on how to better do this? Is what I’m trying to do even possible?

2

My current workaround is to manage the association query and respective mapping myself. Most of the code from the gist remains the same, but the entity and repository have changed:

module Types
  include Dry.Types()
end

module Entities
  class Category < ROM::Struct
    attribute? :ancestors, Types::Array.of(self)
  end
end

class CategoryRepo < ROM::Repository[:categories]
  struct_namespace Entities

  def with_ancestors
    initial = categories.to_a
    return [] if initial.empty?

    collected_ancestors = ancestors(initial.map(&:id)).uniq
    initial.map { |category|
      category.new(ancestors: collected_ancestors.select { |ancestor|
        category.path.value.split(".").include?(ancestor.id.to_s)
      })
    }
  end

  def with_ancestors_by_pk(id)
    initial = categories.by_pk(id).one
    initial.new(ancestors: ancestors(initial.id))
  end

  private

  def ancestors(ids)
    categories.full_hierarchy(id: ids).to_a
  end
end

This works, and it’s probably fine in my particular case given I’m not running this code often. It’d be nice if I could wrap the LTree data type into something custom that split up the path into an array of labels (and memoized the value), but my first attempts at that didn’t work out, and I’ve had other priorities.

This solution does keep things to single separate queries for the core categories and the ancestor categories - which is what I was hoping for from the association initially. Granted, if there’s a way to wrap this up into the association logic anyway, that’d be even better. :smiley: