SQLite tree structures and queries to get sub-trees

SQLite is wonderful!

Just today I found that SQLite support queries with a recurse to get the inner sub-trees:

CREATE TABLE els (
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE
  ,pid INTEGER DEFAULT NULL REFERENCES els
  ,name TEXT
);
INSERT INTO els (id, pid, name) VALUES (1, NULL, 'top');
INSERT INTO els (id, pid, name) VALUES (2, 1, 'x2');
INSERT INTO els (id, pid, name) VALUES (3, 1, 'x1');
INSERT INTO els (id, pid, name) VALUES (4, 3, 'y2');
INSERT INTO els (id, pid, name) VALUES (5, 3, 'y1');
INSERT INTO els (id, pid, name) VALUES (6, 1, 'x3');
INSERT INTO els (id, pid, name) VALUES (7, 6, 'w2');
INSERT INTO els (id, pid, name) VALUES (8, 6, 'w1');
INSERT INTO els (id, pid, name) VALUES (9, 7, 'z1');
INSERT INTO els (id, pid, name) VALUES (10, 7, 'z2');

top
|-x1
| |-y1
| |-y2
|-x2
|-x3
  |-w1
  |-w2
    |-z1
    |-z2

We can write SQL query to get all children of element x3 (id=6):

WITH RECURSIVE
  els_cte(id,pid,name) AS (
    SELECT id,pid,name
        FROM els
        WHERE pid=6
    UNION ALL
        SELECT x.id,x.pid,x.name
            FROM els AS x
            INNER JOIN els_cte AS y ON (x.pid=y.id)
  )
SELECT id,pid,name
    FROM els_cte;

Results are:

7	6	w2
8	6	w1
9	7	z1
10	7	z2
This entry was posted in Blog, Ideas, Misc, Research, SQLite. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Clef two-factor authentication