I have a Topics model that form a hierarchy with a
parent_id key. I’d like to quickly be able to access the path without doing recursive selects so I decided to add and maintain a
hierarchy field. The model looks something like this:
defmodule App.Topic do use App.Web, :model schema "topics" do field :name, :string field :hierarchy, :string belongs_to :parent, App.Topic, foreign_key: :parent_id end end
I’m using Postgres ltrees behind the scenes but the
hierarchy value looks like a string with a dot delimiter:
"1.2.5". Each numeral represents the
id of an ancestor topic.
The main question is: how can I load a topic, with its ancestor topics preloaded?
One promising route appeared to be preload functions but I could not get it to work. In the controller I had:
query = from t in App.Topic, where: fragment("hierarchy @> (SELECT hierarchy FROM topics WHERE id = ?)", ^id) topic = Repo.get!(Topic, id) |> Repo.preload([ancestors: ^query])
has_many :ancestors, App.Topic, foreign_key: :parent_id to get Ecto from complaining about foreign keys but this might be the wrong approach completely–it’s just something I tried.