How to perform recursive query in spanner

Question

I have the following table structure in spanner, I would like to perform a recursive query as shown below in spanner. Can anyone please help to perform the below query.

It seems Recursive queries are not supported in spanner. So I like to perform query for some hierarchical data.

create table category
(
   id               integer      not null primary key,
   name             varchar(100) not null,
   parent_category  integer references category
);

id | name              | parent_category
---+-------------------+----------------
 1 | Root Node         |          (null)
 2 | Software          |               1
 3 | Hardware          |               1
 4 | Notebooks         |               3
 5 | Phones            |               3
 6 | Applications      |               2
 7 | Database Software |               2
 8 | Relational DBMS   |               7
 9 | Tools             |               7
10 | Commandline tools |               9
11 | GUI Tools         |               9
12 | Android Phones    |               5
13 | iPhone            |               5
14 | Windows Phones    |               5

To (recursively) query all sub-categories for one parent, the following query can be used:

with recursive cat_tree as (
   select id,
          name,
          parent_category
   from category
   where name = 'Database Software'  -- this defines the start of the recursion
   union all
   select child.id,
          child.name,
          child.parent_category
   from category as child
     join cat_tree as parent on parent.id = child.parent_category -- the self join to the CTE builds up the recursion
)
select *
from cat_tree;

The above query will return the following result:

id | name              | parent_category
---+-------------------+----------------
 7 | Database Software |               2
 8 | Relational DBMS   |               7
 9 | Tools             |               7
10 | Commandline tools |               9
11 | GUI Tools         |               9

Answer 1

Cloud Spanner does not currently support recursive queries. As a workaround, you would need to implement this in your application logic, looping over non-recursive queries to category.