Skip to content

Instantly share code, notes, and snippets.

@gabidavila
Created February 23, 2019 00:43
  • Star 7 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save gabidavila/67111ce4cb032ae7ea0f92d1a6f1bd6f to your computer and use it in GitHub Desktop.
WITH RECURSIVE traversed (id, name, path, `left`, `right`) AS (
SELECT id,
name,
CAST(JSON_ARRAY(id) AS JSON),
`left`,
`right`
FROM binary_tree
WHERE id = 1
UNION
SELECT b.id,
b.name,
JSON_ARRAY_APPEND(t.path, '$', b.id),
b.`left`,
b.`right`
FROM traversed t
LEFT JOIN binary_tree b
ON b.`id` = t.`left`
WHERE t.`left` IS NOT NULL
UNION
SELECT b.id,
b.name,
JSON_ARRAY_APPEND(t.path, '$', b.id),
b.`left`,
b.`right`
FROM traversed t
LEFT JOIN binary_tree b
ON b.`id` = t.`right`
WHERE t.`right` IS NOT NULL
)
SELECT json_length(path) AS depth,
id,
name,
path,
`left`,
`right`
FROM traversed;
id name left right
1 Ernestina Timer 2 5
2 Cida 3 4
3 Julia NULL NULL
4 Gabriela 6 NULL
5 Marinete 7 8
6 Ada NULL NULL
7 Janaina NULL NULL
8 Weverton 9 NULL
9 Gabriel NULL NULL
depth id name path left right
1 1 Ernestina Timer [1] 2 5
2 2 Cida [1, 2] 3 4
2 5 Marinete [1, 5] 7 8
3 3 Julia [1, 2, 3] NULL NULL
3 7 Janaina [1, 5, 7] NULL NULL
3 4 Gabriela [1, 2, 4] 6 NULL
3 8 Weverton [1, 5, 8] 9 NULL
4 6 Ada [1, 2, 4, 6] NULL NULL
4 9 Gabriel [1, 5, 8, 9] NULL NULL
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment