How to find all children of a parent recursively

Questions / Help
1

I have an Ecto Model as LabTest.Schema, it has

  1. name
  2. id
  3. parent_id

I am trying to get all children of a root LabTest, such

there is a LabTest, Microbiology (name), it doesn’t have any parent_id, but it has an id, and that Id is parent to another lab test, and those lab tests can have more lab tests children

a LabTest is

%LabTest.Schema{
  __meta__: #Ecto.Schema.Metadata<:loaded, "lab_tests">,
  id: "ca394ae8-087f-4f5b-a7a3-0f530e6ae5e2",
  name: "Adenocorticotropic Hormone (ACTH)",
  parent_id: "5bcc307c-7540-4256-a68e-553d1ed34532",
}

there is a hierarchical structure between all lab tests, like a tree.

LabTest (this has an ID)
   -> Children (This has a parent_id, and an ID)
   -> Children (This has a parent_id, and an ID)
      -> Children (This has a parent_id of above Parent children ID, and an ID)
      -> Children (This has a parent_id of above Parent children ID, and an ID)
      -> Children (This has a parent_id of above Parent children ID, and an ID)

I have a root LabTest Name (Microbiology): of lab tests (Which doesn’t have any parent_id), and I am trying to find all its children’s names,

Any help would be wonderful thank you.

1 Like
2

What does your schema look like? I have something similar setup with Categories. Any category can be a root category or have a parent category. Eq.:

...
    belongs_to :parent, Schema.Category, foreign_key: :parent_id
    has_many :children, Schema.Category, foreign_key: :parent_id
...

That way, I don’t necessarily have to fight Ecto when trying to (pre)load parents or children recursively.

In your case, the recursive approach will work as well, I just have to assume that the parent_ids are “raw” and the required fetch/load logic is up to you to define.

There is also a package for more complicated approach - GitHub - coryodaniel/arbor: Ecto elixir adjacency list and tree traversal. Supports Ecto versions 2 and 3.

Or here’s the recursive approach I have adapted to my needs - Recursive models with ecto · GitHub

1 Like
3

Here’s a CTE example:

create table lab_tests (name text, id int, parent_id int);
insert into lab_tests (name, id, parent_id) values
  ('1', 1, null),
  ('1.2', 2, 1),
  ('1.3', 3, 1),
  ('1.3.4', 4, 3),
  ('5', 5, null),
  ('5.6', 6, 5);

with recursive children as (
  select * from lab_tests where id = 1
  union all
  select lab_tests.* from lab_tests, children where lab_tests.parent_id = children.id
) select * from children where id != 1;

Result:

name id parent_id
1.2 2 1
1.3 3 1
1.3.4 4 3
3 Likes
4

its like this

  defmodule Schema do

    schema "lab_tests" do

      field(:name)
      belongs_to(:parent, __MODULE__, on_replace: :nilify)
      has_many(:children, __MODULE__, foreign_key: :parent_id)
end
5

Both of the approaches here will work then

6

great, one question though, why do we have Recursive models with ecto · GitHub limit of 10?

7

It’s an arbitrary magic number for limitation purposes. Tweak it to your own needs and limits.

8

Note that unlike a cte, this approach makes a request to the database for each new tree branch.

1 Like
9

for CTE

id = 1

initial_query = 
  LabTest
  |> where([c], is_nil(c.parent_id))

recursion_query =
  LabTest
  |> join(:inner, [c], ct in "cte", on: c.parent_id == ct.id and c.parent_id == ^id)

lab_test_query =
  initial_query 
  |> union_all(^recursion_query)


LabTest
|> recursive_ctes(true)
|> with_cte("cte", as: ^lab_test_query)

I got to this but it gives me everything in the database but not what I need.

can you please guide me through it? I have never used CTE before.

10

You need to limit the initial query to the id of the parent you are searching for.

[UPDATED] Translating my sql from above:

id = 1

initial_query = where(LabTest, [l], l.id == ^id)
recursion_query = join(LabTest, :inner, [l], c in "children", on: l.parent_id == c.id)
children_query = union_all(initial_query , ^recursion_query)

{"children", LabTest}
|> recursive_ctes(true)
|> with_cte("children", as: ^children_query)
|> where([l], l.id != ^id)
3 Likes
11

thanks, I tested the query and it works but the Translated SQL says:

Postgrex.Error) ERROR 42P01 (undefined_table) relation “cte” does not exist

12

I’ve updated my answer a few times, and you probably copied it when it was incorrect. Can you try again? Can you paste the query you used here?

13

I just used the above query directly to db

with recursive children as (
  select * from lab_tests where id = '84e370b1-7a17-47bb-b65a-9f0d16aba93d'
  union all
  select lab_tests.* from lab_tests, children where lab_tests.parent_id = children.id
) select * from children where id != '84e370b1-7a17-47bb-b65a-9f0d16aba93d';

and it worked as expected but with code as I did

    initial_query = where(LabTest, [l], l == ^id)
    recursion_query = join(LabTest, :inner, [l], c in "children", on: l.parent_id == c.id)
    children_query = union_all(initial_query , ^recursion_query)

    LabTest
    |> recursive_ctes(true)
    |> with_cte("children", as: ^children_query)
    |> where([l], l.id != ^id)
    |> Repo.all()

I should have worked as expected but it throws error as

** (DBConnection.EncodeError) Postgrex expected a tuple, got "4f8d9ec3-f491-4385-afb2-c3e9286034ee". Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.

14

I see,

initial_query = where(LabTest, [l], l == ^id)

this has to be

initial_query = where(LabTest, [l], l.id == ^id)
``
15

there might be a slight problem with the Ecto query,

it gives the same result for everything. no matter what Id is.

with recursive children as (
  select * from lab_tests where id = '84e370b1-7a17-47bb-b65a-9f0d16aba93d'
  union all
  select lab_tests.* from lab_tests, children where lab_tests.parent_id = children.id
) select * from children where id != '84e370b1-7a17-47bb-b65a-9f0d16aba93d';

this works though.

16

Note that you need to select from children, not lab_tests.

{"children", LabTest}
|> recursive_ctes(true)
|> with_cte("children", as: ^children_query)
|> where([l], l.id != ^id)
|> Repo.all()
17

Thanks its actually,

to_query({source, schema} = from)

so it has to be {“children”, LabTest}

18

hey @ruslandoga , out of curiosity, is it also possible to find all parents of children? OR the root parent for every child? through recursive method?

19

Yes, you’d start at the child id and walk the tree in the opposite direction.