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.
