» Scimore News Blog

Recursive Queries with CTE (Common Table Expressions)

2010-05-19 - Marius Slyzius

Since 3...1927 ScimoreDB supports recursive CTE queries. Using recursive CTE it is possible to perform SQL queries on hierarchical data. The SQL syntax is identical to SQL Server and there are many articles about it. For example:

http://sqlblog.com/blogs/linchi_shea/archive/2009/04/16/recursive-sql-queries-how-do-they-work.aspx http://msdn.microsoft.com/en-us/library/ms186243.aspx

Hierarchical relations can be expressed as a recursive single tables' join. For example, consider table:

create table objects
(
id int not null primary key,
name varchar,
parent_id int
); 

And insert:

insert into objects(id,name,parent_id) values(1,'Cars',null);
insert into objects(id,name,parent_id) values(2,'Porsche',1);
insert into objects(id,name,parent_id) values(3,'911',2);
insert into objects(id,name,parent_id) values(4,'Boxster',2);
insert into objects(id,name,parent_id) values(5,'Cayman',2);
insert into objects(id,name,parent_id) values(6,'Cayenne',2);
insert into objects(id,name,parent_id) values(7,'Panamera',2);
insert into objects(id,name,parent_id) values(8,'Aston Martin',1);
insert into objects(id,name,parent_id) values(9,'DB7',8);
insert into objects(id,name,parent_id) values(10,'DB9',8);
insert into objects(id,name,parent_id) values(11,'Vantage',8);
insert into objects(id,name,parent_id) values(12,'One',8); 

Here we have created hierarchy using parent_id field that refers to the parent row: "Cars" is the root (no parent), 2 brands (Porsche and Aston Martin) belongs to "Cars" and each brand has a list of models. Now, define the recursive CTE SQL to read all Porsche models:

WITH Cars(id,parent_id,name,path,level) AS
(
    --initialization. read porsche root
    SELECT id,parent_id,name,name,0 as level
    FROM objects
    WHERE id = 2 -- porsche root
    UNION ALL
    --recursive execution
    SELECT o.id,o.parent_id,o.name,concat(c.path,'/', o.name), c.level+1
    FROM cars c
    INNER JOIN objects o ON c.id = o.parent_id
)
select * from cars; 

The result:

id Parent_id Name Path Level

2 1 Porsche Porsche 0 3 2 911 Porsche/911 1 4 2 Boxster Porsche/Boxster 1 5 2 Cayman Porsche/Cayman 1 6 2 Cayenne Porsche/Cayenne 1 7 2 Panamera Porsche/Panamera 1

Example using parameterized query and ordering:

Declare @id int
set @id = select id from objects where name = 'porsche';

WITH Cars(id,parent_id,name,path,level) AS
(
    --initialization. read porsche root
    SELECT id,parent_id,name,name,0 as level
    FROM objects
    WHERE id = @id
    UNION ALL
    --recursive execution
    SELECT o.id,o.parent_id,o.name,concat(c.path,'/', o.name), c.level+1
    FROM cars c
    INNER JOIN objects o ON c.id = o.parent_id
)
select * from cars order by name;