graasp / graasp/graasp-api

Remove propagation of the LTREE in other tables

Open
#1,649 0 comments 0 reactions 0 assignees View on GitHub
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.