Remove propagation of the LTREE in other tables
- Dominant language
- TypeScript
- Stars
- 7
- Forks
- 5
- PR merge metrics
- No merged PRs in 30d
Description
Remove propagation of the LTREE in other tables using a view or function to abstract the way we retrieve children, descendants or ancestors.
- [ ] Evaluate the performances of this implementation
- [ ] Improve the view if necessary
```SQL
CREATE OR REPLACE VIEW item_hierarchy AS
SELECT
descendant.id AS descendant_id,
ancestor.id AS ancestor_id,
CASE
WHEN nlevel(descendant.path) - nlevel(ancestor.path) = 1 THEN TRUE
ELSE FALSE
END AS is_direct_parent
FROM item descendant
JOIN item ancestor ON ancestor.path @> descendant.path
WHERE descendant.id <> ancestor.id;
-- Get all descendants of root item:
SELECT descendant_id
FROM item_hierarchy
WHERE ancestor_id = 'ROOT_ID';
-- Get all children of root item:
SELECT descendant_id
FROM item_hierarchy
WHERE ancestor_id = 'ROOT_ID'
AND is_direct_parent IS TRUE;
-- Get all ancestors of a given item:
SELECT ancestor_id
FROM item_hierarchy
WHERE descendant_id = 'DESCENDANT_ID';
-- Example with a join using the LTREE:
SELECT
a.*,
i.*
FROM action a
INNER JOIN item i ON a.item_id = i.id
WHERE i.path <@ 'ROOT_PATH'
AND i.id <> 'ROOT_ID'
ORDER BY a.created_at
LIMIT 10;
-- And using the View:
SELECT
a.*,
i.*
FROM action a
INNER JOIN item_hierarchy ih ON a.item_id = ih.descendant_id
INNER JOIN item i ON a.item_id = i.id
WHERE ih.ancestor_id = 'ROOT_ID'
ORDER BY a.created_at
LIMIT 10
```
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.