There are a few ways to do this. I put a trigger on the category table
to build an ancestor table. This allows you to get all the records
with one sql statement by joining the ancestor table with the category
table where ancsestorid = root node. I only do inserts in to my table.
If you update you would also need update triggers.
drop table CategoryAncestors;
create table CategoryAncestors (
categoryId bigint,
ancestorId bigint,
generation int
);?
drop trigger CategoryPInsert;
create trigger CategoryPInsert
after insert on Categories
REFERENCING NEW as NewCategory
for each row mode DB2SQL
insert into CategoryAncestors ( categoryId, ancestorId, generation )
(
select NewCategory.categoryId, ancestorId, generation + 1
from CategoryAncestors
where categoryId = NewCategory.parentId
union
select NewCategory.categoryId, NewCategory.categoryId, 0 from sysibm.sysdummy1
);
drop trigger CategoryInsert;
create trigger CategoryInsert
after insert on Categories
REFERENCING NEW as NewCategory
for each row mode DB2SQL
insert into CategoryAncestors ( categoryId, ancestorId, generation )
(select NewCategory.categoryId, NewCategory.categoryId, 0 from
sysibm.sysdummy1
);
