Get all elements of hierarchy

[martin1]

Dear all, I try to accomplish the following:
* I want to write an SQL query returning all elements (e.g. requirements, classes, blocks) of a package hierarchy
* The only parameter is the Package_ID of the "root" node
* No recursion mechanisms, no macros
* The number of levels to go down the hierarchy can be fixed and set to e.g. 3

My current idea was to use t_object and join t_package multiple times and get the elements. But it doesn't work yet.

Code: [Select]

SELECT *
FROM ((((t_object AS o
INNER JOIN t_package AS p0 ON o.Package_ID = p0.Package_ID)
LEFT JOIN t_package AS p1 ON p1.Parent_ID = p0.Package_ID)
LEFT JOIN t_package AS p2 ON p2.Parent_ID = p1.Package_ID)
LEFT JOIN t_package AS p3 ON p3.Parent_ID = p2.Package_ID)
WHERE p0.Package_ID = <number>

Any ideas what's missing and how to realize this?

[Geert Bellekens]

Code: [Select]

p0 ON o.Package_ID = p0.Package_IDMeans you only want elements that are directly under p0.

What you need is to end with t_object o and join that with any of the package ID's.

Code: [Select]

inner join t_object o on o.package_ID in (p0.package_ID, p1.package_ID, p2.package_ID, p3.Package_ID)
Geert

[martin1]

So in total, this would read:

Code: [Select]

SELECT *
FROM ((((t_object AS p0
LEFT JOIN t_package AS p1 ON p1.Parent_ID = p0.Package_ID)
LEFT JOIN t_package AS p2 ON p2.Parent_ID = p1.Package_ID)
LEFT JOIN t_package AS p3 ON p3.Parent_ID = p2.Package_ID)
INNER JOIN t_object AS o on o.Package_ID in (p0.Package_ID, p1.Package_ID, p2.Package_ID, p3.Package_ID))
WHERE p0.Package_ID = <number>
I get a syntax error on that one? I am not so sure if IN can be used within JOIN?

[Geert Bellekens]

t_object AS p0 should be t_package as p0 I guess.

And you shouldn't use the "AS" keyword for table aliasses.

You can definitely use IN in a join. I'm not sure which database you are working with, but if the IN doesn't work, you can replace that with OR's.

Geert

[Richard Freggi]

What's wrong with recursive, I find it easier and safer.
You can get all package GUIDs then from there get all elements within that package

Code: [Select]

WITH RECURSIVE pkgbranch (eaguid, pkgid, pkgname, parentid, lvl) AS
(SELECT p.ea_guid, Package_ID, p.Name, p.Parent_ID, 1
FROM t_package p
WHERE p.ea_guid = '{xxxx}'
UNION ALL
SELECT p.ea_guid, p.Package_ID, p.Name, p.Parent_ID, pb.lvl+1
FROM t_package p
JOIN pkgbranch pb ON pb.pkgid = p.Parent_ID)

SELECT * FROM pkgbranch;


[Geert Bellekens]

What's wrong with recursive, I find it easier and safer.
You can get all package GUIDs then from there get all elements within that package

Code: [Select]

WITH RECURSIVE pkgbranch (eaguid, pkgid, pkgname, parentid, lvl) AS
(SELECT p.ea_guid, Package_ID, p.Name, p.Parent_ID, 1
FROM t_package p
WHERE p.ea_guid = '{xxxx}'
UNION ALL
SELECT p.ea_guid, p.Package_ID, p.Name, p.Parent_ID, pb.lvl+1
FROM t_package p
JOIN pkgbranch pb ON pb.pkgid = p.Parent_ID)

SELECT * FROM pkgbranch;


You can't use recursive queries in EA searches, or Repository.SQLQuery
They need the query to start with Select

Geert

[martin1]

t_object AS p0 should be t_package as p0 I guess.

Of course you're right.

You can definitely use IN in a join. I'm not sure which database you are working with, but if the IN doesn't work, you can replace that with OR's.

What is the goal of your last INNER JOIN, is the IN statement a shortcut to perform a JOIN 4x? Or JOIN once, and filter results?

Btw, INNER JOIN doesn't seem to work here ("expression not supported"). LEFT JOIN seems to bypass this.

[martin1]

You can definitely use IN in a join. I'm not sure which database you are working with, but if the IN doesn't work, you can replace that with OR's.

When I replace X with 0,1,2,3 it seems to work for each individual level. Any ideas how to achieve this in one query?

Code: [Select]

SELECT *
FROM ((((t_package p0
LEFT JOIN t_package p1 ON p1.Parent_ID = p0.Package_ID)
LEFT JOIN t_package p2 ON p2.Parent_ID = p1.Package_ID)
LEFT JOIN t_package p3 ON p3.Parent_ID = p2.Package_ID)
LEFT JOIN t_object o ON o.Package_ID = pX.Package_ID)
WHERE p0.Package_ID = <number>
Edit: 4x UNION seems to do the trick, but I think this is the least elegant solution.

[Geert Bellekens]

LEFT JOIN t_object o ON (o.Package_ID = p0.Package_ID or o.package_ID = p1.Package_ID or...)

Geert

[martin1]

LEFT JOIN t_object o ON (o.Package_ID = p0.Package_ID or o.package_ID = p1.Package_ID or...)

I already tried that, this gives me a syntax error in EA14.

Thanks for your support!

[BobM]

Code: [Select]

SELECT
CLASSGUID = o.ea_guid
,CLASSTYPE = o.object_type
    , o.*
FROM ((((t_package AS p0
LEFT JOIN t_package AS p1 ON p1.Parent_ID = p0.Package_ID)
LEFT JOIN t_package AS p2 ON p2.Parent_ID = p1.Package_ID)
LEFT JOIN t_package AS p3 ON p3.Parent_ID = p2.Package_ID)
INNER JOIN t_object AS o on o.Package_ID in (p0.Package_ID, p1.Package_ID, p2.Package_ID, p3.Package_ID))
WHERE 1=1
AND p0.Package_ID = <number>

[martin1]

Code: [Select]

SELECT
CLASSGUID = o.ea_guid
,CLASSTYPE = o.object_type
    , o.*
FROM ((((t_package AS p0
LEFT JOIN t_package AS p1 ON p1.Parent_ID = p0.Package_ID)
LEFT JOIN t_package AS p2 ON p2.Parent_ID = p1.Package_ID)
LEFT JOIN t_package AS p3 ON p3.Parent_ID = p2.Package_ID)
INNER JOIN t_object AS o on o.Package_ID in (p0.Package_ID, p1.Package_ID, p2.Package_ID, p3.Package_ID))
WHERE 1=1
AND p0.Package_ID = <number>

Thanks. In EA14 this does not work, EA reports a redundant bracket "(".

[BobM]

a bit more simplified in brackets (is always the same result)

I would genuinely be surprised if they ever did updates on the SQL editor (I would be happy, but it's definitely not on the priority list of sparx devs)

Code: [Select]

SELECT
CLASSGUID = o.ea_guid
,CLASSTYPE = o.object_type
    , o.*
FROM t_package AS p0
LEFT JOIN t_package AS p1 ON p1.Parent_ID = p0.Package_ID
LEFT JOIN t_package AS p2 ON p2.Parent_ID = p1.Package_ID
LEFT JOIN t_package AS p3 ON p3.Parent_ID = p2.Package_ID
INNER JOIN t_object AS o on o.Package_ID in (p0.Package_ID, p1.Package_ID, p2.Package_ID, p3.Package_ID)
WHERE 1=1
AND p0.Package_ID = <number>

[martin1]

In this old version you have to use brackets for multiple JOINs, to avoid confusion of the SQL engine.

[BobM]

I never realized the SQL engine used to be worse as the one we currently have to use.