HIERARCHICAL (RECURSIVE) QUERIES

These queries are allowed for recursive links only (Recursive link is defined for the single table, i.e. it defines relationships between the records. Such table also is known as hierarchical table (it contains tree-like data).

A recursive query returns an appropriate recordset from the table on which the recursive link is defined. The returned cursor consists of all original fields of this table.

vext_recursive_table         -- v4.1
    :   {ANCESTORS | BROTHERS | DESCENDANTS} OF vext_root_objects 
        [{TO|ON} LEVEL uint_or_var] 
        USING ( link_name | search_condition )
        [WITH {ROOT | ROOTS}]
        [WITH {COLUMN | COLUMNS} vext_pseudo_column, ... ]          // added IN v5.0
 
vext_root_objects
    :   UINT
    |   variable_name
    |   ( search_condition )
 
vext_pseudo_column
    :  {LEVEL | IS_LEAF} [[AS] IDENT]
 
uint_or_var
    :   UINT
    |   variable_name   

• {ANCESTORS | BROTHERS | DESCENDANTS} - specifies what kind of objects we want to find using recursive link.

• vext_root_objects - is a record or set of records for which the operation is performed.

It could be defined as a particular RecID, as some identifier (suppose to be a variable name) or an any expression (valid for this hierarchical table).

• ON LEVEL N - means to collect records on the Nth recursion level only.
• TO LEVEL N - means to collect records on all levels in the range [1,N].

NOTE: Omitting the 'ON LEVEL N' or 'TO LEVEL N' argument defaults to ALL LEVELS (in the specified direction). This means that all Ancestors|Brothers|Descendants will be returned in the result set.

• USING (link_name | search_condtion) - a recursive link. It can be link of any kind: RDB, ObjectPtr or BinaryLink.

• WITH ROOT(S) - means to include start-point root record(s) into the result. ROOT and ROOTS are synonyms. They are provided both just to have a nice English phrase when you are going to work with a SINGLE root record or with a SET of root records. Note, that the SQL parser is not strict here and will not stop you if you will use ROOTS with a single record.

• WITH COLUMNS - allows to produce additional pseudo-columns in the result table. See details below.

• Link is not found
• Link is not Recursive

Working with this command you need specify the following information:

1. kind of related records that we want to find: ANCESTORS, DESCENDANTS or BROTHERS.
2. what record or records use as root(s) of a hierarchy tree to walk.
3. what levels you going collect records from.
4. what self-recursive LINK we will use to navigate between records of the tree.
5. if include ROOT record(s) into the result table.
6. if add some special columns into the result table.

The result of this command is virtually a new table, which have the same fields as original table, and contains the specified records. In the best case this command even do not produce any temporary table. You can think about this commands as one more particular kind of VIEW.

This command can search in the following directions:

• DOWN by hierarchy starting from some parent record to find its child-records.
• UP by hierarchy to find parent-records.
• WIDE by hierarchy to find brother-records.

NOTE: a brother of X-record is a record Y, which is child of the same parent-record.

Descendants

-- get children for recID=2:
--
DESCENDANTS OF 2 ON LEVEL 1 USING link1

-- get grandchildren for recID=2 and 3:
--
DESCENDANTS OF (RecID IN (2,3)) ON LEVEL 2 USING link1

-- get children and grandchildren for recID=2 and 3:
--
DESCENDANTS OF (RecID IN (2,3)) TO LEVEL 2 USING link1

Ancestors

-- get parents for recID=2:
--
ANCESTORS OF 2 ON LEVEL 1 USING link1

-- get grandparents for recID=2 and 3:
--
ANCESTORS OF (RecID IN (2,3)) ON LEVEL 2 USING link1

-- get parents and grandparents for recID=2 and 3:
--
ANCESTORS OF (RecID IN (2,3)) TO LEVEL 2 USING link1

Brothers

-- get the brothers for recID=2:
--
BROTHERS OF 2 ON LEVEL 1 USING link1

-- get the cousins for recID=2 and 3:
--
BROTHERS OF (RecID IN (2,3)) ON LEVEL 2 USING link1

-- get the brothers and cousins for recID=2 and 3:
--
BROTHERS OF (RecID IN (2,3)) TO LEVEL 2 USING link1

This command gives you very powerful way to specify a root record, using its RecID or Primary Key, or set of root records, using search condition of any complexity as it can be in the WHERE clause.

WITH ROOT(S) suffix also add to result the root record(s) from which we start search by recursion.

This command allows you to search records ON some recursion level only or collect all records up to some level. If you do not specify LEVEL at all then all recursive records will be collected into the result.

Important to note, that Valentina command specifies not a table with self-recursion, but a Link, which establishes this recursion. It is obvious that usage of Link is more wise. Really, on the same table can exists more than one Links, each of which defines its own hierarchy of objects. Since the Link must be recursive, it specifies the table explicitly.

NEW in v5.0

The optional suffix [WITH COLUMNS …] allows you add even more useful information into the result of a recursive query. You can add one or few of the following pseudo-columns:

• LEVEL - is a ULONG column, which contains the number of hierarchy level for its record. A root object has level with number 1, its child-objects are on level 2, and so on.
• IS_LEAF - is a BOOLEAN column, contains 1 if this is a leaf record, i.e. do not child-objects. This column can be useful for GUI applications, to help easy decide if this row can be expanded to show child rows.

You can use COLUMN or COLUMNS keywords as you want, this not changes result. Two forms provided just for good reading of the query.

Example:

DESCENDANTS OF (id=4) USING link1 WITH ROOTS WITH COLUMN LEVEL  

DESCENDANTS OF (id=4) USING link1 WITH ROOTS WITH COLUMNS LEVEL, IS_LEAF 

You can also specify the alias name for pseudo-columns. You may beed this mainly if your table already have columns with names as “LEVEL” and “IS_LEAF”.

DESCENDANTS OF (id=4) USING link1 WITH ROOTS WITH COLUMN LEVEL AS HLevel 

Using wrapping SELECT you can do even more things: rename columns or do some calculations:

SELECT (LEVEL + 1) AS MyLevel, *
FROM (DESCENDANTS OF (id=4) USING link1 WITH ROOTS WITH COLUMN LEVEL)

Important to note that this recursive command is a query_primary (see Valentina SQL Grammar).

This means that you can use it in the following forms:

1) Just this rule. Result is table with the same fields as original recursive table.

DESCENDANTS OF 2 ON LEVEL 1 USING link1;

2) As part of the FROM clause. Result is identical to above form if to use SELECT *, but you can use SELECT f1, f2, … to get different result.

SELECT * 
FROM (DESCENDANTS OF 2 ON LEVEL 1 USING link1)

3) As IN (sub-query)

SELECT * 
FROM T
WHERE fldPtr IN (SELECT RecID FROM (DESCENDANTS OF 2 ON LEVEL 1 USING linkT2T2))

• Article: Hierarchical (Recursive) Queries in Valentina SQL