New post
Question
· Mar 12, 2021

Hierarchical Queries

#Caché

Hi community,

I need to write an SQL query with hierarchical order, I have a table with a column referencing itself, similar as the sample bellow:

ID
DATE
MESSAGE
LOGIN
PARENT_ID
1
27/01/21
 Bacon ipsum dolor amet pork shoulder ribs
User 1
 
2
27/01/21
 Gouda croque monsieur emmental.
User 2
1
3
27/01/21
 Manchego fromage frais airedale
User 3
2

Oracle database has Hierarchical Query, to do something like that:

SELECT id, MESSAGE, parent_id
   FROM messages
   CONNECT BY PRIOR id = parent_id;

Is it possible to do something like that in Caché?

Thanks in advance

Product version: Caché 2018.1
$ZV: Cache for UNIX (Oracle Solaris for SPARC-64) 2018.1.3 (Build 414_0_20009) Wed Jun 17 2020 12:17:10 EDT
Discussion (4)1
Log in or sign up to continue
Timothy Leavitt · Mar 12, 2021

There's nothing built-in for this, but you can simulate it via custom class queries or %SQL.AbstractFind. I have an implementation of %SQL.AbstractFind/%Library.FunctionalIndex that does some things with hierarchies but falls short of the capabilities you linked in the Oracle doc. Specifically, it can find all ancestors/descendants/both (the whole tree) in a hierarchy efficiently, but it doesn't follow the same rules around ordering and won't let you do paths and such. (I'd want to clean it up a good deal before sharing, but that's probably worthwhile at some point.)

0 0
Timothy Leavitt · Mar 12, 2021

With what we've done the syntax ends up looking like:

Class DC.Demo.Hierarchy Extends %Persistent [ MemberSuper = AppS.Index.Methods ]
{

Property message As %String;

Property login As %String;

Property parentId As DC.Demo.Hierarchy [ SqlFieldName = parent_id ];

Index parentId On parentId [ Type = bitmap ];

ClassMethod RunDemo()
{
    Do ..%KillExtent()
    &sql(insert into DC_Demo.Hierarchy (message, login, parent_id)
        values ('Bacon ipsum dolor amet pork shoulder ribs', 'User 1', null))
    &sql(insert into DC_Demo.Hierarchy (message, login, parent_id)
        values ('BGouda croque monsieur emmental.', 'User 2', 1))
    &sql(insert into DC_Demo.Hierarchy (message, login, parent_id)
        values ('Manchego fromage frais airedale', 'User 3', 2))
        
    Do ##class(%SQL.Statement).%ExecDirect(,
        "select id, message, parent_id from DC_Demo.Hierarchy "_
        "where id %FIND DC_Demo.Hierarchy_parentIdFind(2,'all descendants')").%Display()
        
    Do ##class(%SQL.Statement).%ExecDirect(,
        "select id, message, parent_id from DC_Demo.Hierarchy "_
        "where id %FIND DC_Demo.Hierarchy_parentIdFind(3,'all related')").%Display()
}

}

Because there's a self-referencing property with a bitmap index, the hierarchy support is automatic via the MemberSuper class. Output is:

d ##class(DC.Demo.Hierarchy).RunDemo()
ID    message    parent_id
2    BGouda croque monsieur emmental.    1
3    Manchego fromage frais airedale    2

2 Rows(s) Affected

ID    message    parent_id
1    Bacon ipsum dolor amet pork shoulder ribs    
2    BGouda croque monsieur emmental.    1
3    Manchego fromage frais airedale    2

3 Rows(s) Affected
1 1