How to load Ecto associations from Postgres ltree

Questions / Help
1

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])

I added 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.

2 Likes
2

Is it truly a tree? Because if it’s a tree you can’t get out of recursion. It may even be best to do https://www.postgresql.org/docs/current/static/queries-with.html

Alternatively you can avoid making it a tree. If however you have a tree of arbitrary depth then I don’t see any way out of recursion

1 Like
3

Since Repo.get + Repo.preload is 2 queries on the database then you can be more explicit with no additional database penalty:

# Get the child
topic = Repo.get!(Topic, id)

# And then its ancestors in the ltree
ancestors_query = from t in App.Topic,
  where: fragment("hierarchy @> ?", ^topic.hierachy)

Repo.all(Topic, ancestors_query)

You didn’t mention exactly what the error you got from Ecto was … have you also added an Ecto type for ltree and lquery since these aren’t types defined in Ecto?

4

Not an Ecto expert but you can simply run a raw query to avoid running multiple queries
Ecto.Adapters.SQL.query
https://hexdocs.pm/ecto/Ecto.Adapters.SQL.html#query/4

5

Thanks for the responses everyone.

@benwilson512 Conceptually topics do form a tree. However the hierarchy forms a path so recursion can be avoided. For example the raw Postgres query that selects the ancestors and works perfectly fine would be:

SELECT t0."id", t0."name", t0."icon", t0."color", t0."hierarchy", t0."parent_id", t0."inserted_at", t0."updated_at" FROM "topics" AS t0 WHERE (hierarchy @> (SELECT hierarchy FROM topics WHERE id = '1'));

But I need to convert the results of this query into a proper array of Topic objects and associate that as the ancestors of another proper Topic object.

@kip Yes ltrees and lqueries are working great. I followed this guide and can do raw queries in iex with no problem.

The error I was getting with just the above model is schema App.Topic does not have association :ancestors. So if I add has_many :ancestors, App.Topic to the model I get the error:

deps/ecto/lib/ecto/association.ex:392: field `App.Topic.topic_id` in `where` does not exist in the schema in query:

from t in App.Topic,
  where: fragment("hierarchy @> (SELECT hierarchy FROM topics WHERE id = ?)", ^"1"),
  where: t.topic_id in ^["1"],
  order_by: [asc: t.topic_id],
  select: {t.topic_id, t}

Presumably this is because I did not specify a foreign key and so it sees it is a Topic and assume the foreign key is topic_id. But hierarchy is not in the form it expects as a foreign key but does contain the data for associated ancestor topics. This leads me to has_many :ancestors, App.Topic, foreign_key: :parent_id as posted above because :parent_id is in the form Ecto is expecting but of course is not correct.

@andre1sk I am okay with crafting a raw query, but the problem is how to initialize this into a proper Topic object with an ancestors key that contains an array of other proper Topic objects is the issue.

6

You could do something like:

result = Ecto.Adapters.SQL.query!(App.Repo, qry, [])
col_names =[:ancestors|Enum.map(result.columns, &(String.to_atom(&1)))]
topic = 
  result.rows
  |> Stream.map(fn(row) -> struct(App.Topic, Enum.zip(col_names, [[]|row]) end) 
  |> Stream.map(fn(topic) -> {topic.id, topic} end) 
  |> Map.new
  |>fill_ancestors(id)

fill_ancestors would take a map of topics and fill out ancestors based on :hierarchy and return the topic you need. You could obviously shift code into functions to make things nicer.

7

For those interested I settled on essentially what @kip recommended. Still unclear if this is the “right” way but it worked for me.

Model:

defmodule App.Topic do
  use App.Web, :model

  schema "topics" do
    field :name, :string
    field :hierarchy, :string
    field :ancestors, {:array, :map}, virtual: true
    belongs_to :parent, App.Topic, foreign_key: :parent_id
  end
end

Controller:

def show(conn, %{"id" => id}) do
  topic = Repo.get!(Topic, id)
  ancestors_query = from t in App.Topic,
    where: fragment("hierarchy @> ?", ^topic.hierachy)
  ancestors = Repo.all(ancestors_query)

  topic = Map.merge(topic, %{ ancestors: ancestors })
  render(conn, "show.json", topic: topic)
end
1 Like
8

I have more basic issue with Postgres ltree:

** (ArgumentError) no extension found for oid `1305666`: %Postgrex.TypeInfo{array_elem: 0, base_type: 0, comp_elems: [], input: "ltree_in", oid: 1305666, output: "ltree_out", receive: "-", send: "-", type: "ltree"}
    (postgrex) lib/postgrex/types.ex:228: Postgrex.Types.encoder/2

How did you manage to avoid it?

9

What version of PostgreSQL? Do you have the needed extension installed for it?

10

@denispeplin As stated earlier I followed this guide.

2 Likes
11

Here is an updated link to the guide mentioned by @jhchen:
https://hostiledeveloper.com/2017/06/17/ltrees-in-phoenix.html