SQL - Sorting Hierarchical Data (Child -> Parent Relation)

Question

I'm trying to elegantly show data which is very similar to file system structure.

Table Legend:

Learning Path - something like folder which can contain ExerciseSet or another child LearningPath

ExerciseSet - something like file in file system structure.

LearningPathItem - table which is a reference to either a ExerciseSet or LearningPath. LearningPath is composed from LearningPathItems and these are then connected to ExerciseSet or LearningPath by itemId column.

Example structure:

Learning Path 1
-> Exercise Set 1
-> Learning Path 2
-> -> Exercise Set 2
-> -> Exercise Set 3
-> -> Learning Path 3
-> -> -> Exercise Set 4
-> -> -> Exercise Set 5
-> Exercise Set 6

Here is my CTE SQL Query to recursively traverse through and build a tree based on child -> parent relation (learningPath column on LearningPathItem is a parent here). LearningPathItem table can be either a reference to ExerciseSet(file analogy) or Learning Path (folder analogy).

  WITH RECURSIVE tree_view AS (
    SELECT
        id,
        "itemId",
        TYPE,
        "order",
        "learningPath",
        0 AS level
    FROM
        "LearningPathItem"
    WHERE
        "learningPath" = 'cl5y0o4a60273zas89c0yskey'
    UNION ALL
    SELECT
        child.id,
        child. "itemId",
        child.type,
        child. "order",
        child. "learningPath" AS parent,§
        level + 1 AS level
    FROM
        "LearningPathItem" child
        JOIN tree_view tv ON child. "learningPath" = tv. "itemId"
)
SELECT
    tv.type,
    tv. "itemId",
    lp.title AS "Learning Path Title",
    es.title AS "Exercise Set Title",
    tv. "learningPath" AS parent,
    tv.level,
    tv.order,
    uonlp.efactor AS "Learning Path efactor",
    m.efactor AS "Exercise Set efactor"
FROM
    tree_view tv
    LEFT JOIN "Memoization" m ON m. "exerciseSet" = tv. "itemId"
        AND m. "user" = 'cl524cilt0035d6s8wymsgb4o'
    LEFT JOIN "LearningPath" lp ON lp.id = tv. "itemId"
    LEFT JOIN "ExerciseSet" es ON es.id = tv. "itemId"
    LEFT JOIN "UserOnLearningPath" uonlp ON uonlp. "user" = 'cl524cilt0035d6s8wymsgb4o'
        AND uonlp. "learningPath" = tv. "itemId"
    ORDER BY tv.level, tv.order

Result:

As you can see here I'm simply sorting here by level and order column, when the perfect and desired order is:

Lodash Essentials (set) level 0 order 0
JavaScript Path (path) level 0 order 1
-> JavaScript Essentials (set) level 1 order 0
-> Lodash Essentials 2 (set) level 1 order 1
Root Set (set) level 0 order 2

I'm cracking my head on it whole day and can't even figure out if it's even possible here.

Any help here will be great, I can provide more data if needed.

During the recursion, create an array (`text[]`) to contain the full path from the root to the current row. Then, sort on the resulting array. — Mike Organek, Jul 24 '22 at 15:14
Two questions: 1) How many direct children (as a maximum) does any parent have? 2) Why the child `-> Exercise Set 1` goes before the child `-> Learning Path 2`? — The Impaler, Jul 24 '22 at 16:31
One Parent can have one or more Exercise Sets or Learning Paths and there is no limit here. Exercise Set 1 is going before because order of the elements in the list is important. that's why i have `order` column — artolini, Jul 24 '22 at 17:24

SQL - Sorting Hierarchical Data (Child -> Parent Relation)

0 Answers0