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
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,
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.
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;
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.
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';