SQL find neighbours by stereotype

[Hurra]

Hello!

I'm creating some documentation and usually find elements via scripting, not SQL, which I know, is not the best idea. I've run into some problems sorting an array in JScript, I don't know what really..

Therefore I thought I might try to create a SQL query instead and of course ran in to problems due to my lack of experience..

I want a search where I collect all neighours of a certain stereotype.

My first thought was some kind of JOIN of t_object.stereotype = #<Search Term># and t_connector.End(or Start)_ObjectID=#OBJECTID#. I will have the ObjectID through the script. I'm guessing I somehow have to use xref also?

SELECT obj.ea_guid AS CLASSGUID, obj.Object_Type AS CLASSTYPE, obj.Name AS Name, obj.Note as Description
FROM t_object obj JOIN t_connector con..?
WHERE obj.Stereotype LIKE '#WC#<Search Term>#WC#' AND..?

All help appreciated!

[Geert Bellekens]

So you want to select all elements that have a relationship with an element that has a certain stereotype?

In that case you would need something like:

Code: [Select]

select o.ea_guid as CLASSGUID, o.Object_Type as CLASSTYPE, o.Name
from ((t_object o
inner join t_connector c on c.Start_Object_ID = o.Object_ID)
inner join t_object  o2 on (o2.Object_ID = c.End_Object_ID
and o2.Stereotype = 'MySterotype'))
union
select o.ea_guid as CLASSGUID, o.Object_Type as CLASSTYPE, o.Name
from ((t_object o
inner join t_connector c on c.End_Object_ID = o.Object_ID)
inner join t_object  o2 on (o2.Object_ID = c.Start_Object_ID
and o2.Stereotype = 'MySterotype'))
You need the union if you want to follow the connectors both ways.
This query works only if you have a single stereotype. In case of multiple stereotypes you will indeed need to join t_xref.

Geert

[Hurra]

Thanks Geert!

Do you know anything about the sorting method in JScript?

I have tried both with an array with strings: str = ['a', 'b', ...] and with elements: ele = [ele1, ele2, ... ]

I've tried str.sort() and ele.sort(), ele.Name.sort(), the script always stops after I try to sort the array.

Do I use the sorting function wrong or is it not implemented in EA or something like that..?

Thanks.

[Hurra]

I've tried your SQL search Geert, and I think it is not quite what I'm after.



Suppose I have a known element. From this element I want all neighbours of a certain stereotype.

I have an element with a known objectID X. From objectID X I want all related elements with stereotype Y.

The resulting element set should be a list of elements of stereotype Y, all with a relation to element X.



Perhaps this is what your query does, but I think I get an element set of 'all elements which have a relation to myStereotype'.

[Geert Bellekens]

I don't know anything about sorting methods in Jscript (I only do scripting in VBScript), but I would expect that you get some kind of error when debugging if your script stops.
In VBScript an array doesn't have a sorting method. Could it be the same for Jscript?

If you already know object o and you need the info from object 02 (related object) then you could do something like this:

Code: [Select]

select o2.ea_guid as CLASSGUID, o2.Object_Type as CLASSTYPE, o2.Name
from ((t_object o
inner join t_connector c on c.Start_Object_ID = o.Object_ID)
inner join t_object  o2 on (o2.Object_ID = c.End_Object_ID
and o2.Stereotype = 'MySterotype'))
where o.Object_ID = 123
union
select o2.ea_guid as CLASSGUID, o2.Object_Type as CLASSTYPE, o2.Name
from ((t_object o
inner join t_connector c on c.End_Object_ID = o.Object_ID)
inner join t_object  o2 on (o2.Object_ID = c.Start_Object_ID
and o2.Stereotype = 'MySterotype'))
where o.Object_ID = 123
Geert

[Hurra]

Thanks for your time Geert.

The output of your latest search only gives me the element connected to the 'myStereotype' element. I want to go the other way around. The element list should be a list of 'myStereotype', which all of these elements are related to the known start element.

I will start from scratch, to understand what I'm doing.

Code: [Select]

select o.ea_guid as CLASSGUID, o.Object_Type as CLASSTYPE, o.Name, o.Note AS [Notes] --choose columns to show from t_object
from t_object o --table to show
where o.Stereotype = 'myStereotype' --filter out all other stereotypesThe result is a list of all elements in the model with stereotype 'myStereotype'. Now I want to remove some results, to only show the 'myStereotype' connected to the element with objectID X.

Code: [Select]

select o.ea_guid as CLASSGUID, o.Object_Type as CLASSTYPE, o.Name, o.Note AS [Notes] --choose columns to show from t_object
from t_object o --table to show
inner join t_connector c on c.Start_Object_ID = X --only show rows which match, this doesn't seem to work?
where o.Stereotype = 'myStereotype' --filter out all other stereotypes
This gives me, for what I can see, all the elements in the model which has stereotype 'myStereotype'. And also, what I don't quite understand, the elements are shown several times? So this search actually gives me more results than the previous one, how is that possible?
 Not what I'm looking for.

The INNER JOIN keyword selects records that have matching values in both tables.

A picture says more than a thousand words:

https://drive.google.com/file/d/13xADgCIg863Wbhh4bbJ4EAD4awgK8hJI/view?usp=sharing

I have the starting element object ID x. I want the element list from the query only to show the 'myStereotypes', related to my starting node.


I don't need a working SQL query, since I need to know these kind of stuff. I really want to understand this better.

Thanks for any input. I hope I'm not confusing 



Actually just typing this together made me solve this, I think. This seems to give me what I'm looking for:

Code: [Select]

select o.ea_guid as CLASSGUID, o.Object_Type as CLASSTYPE, o.Name, o.Note AS [Notes]
from t_object o
inner join t_connector c on c.End_Object_ID = o.Object_ID
where o.Stereotype = 'myStereotype' and c.Start_Object_ID = x

[Geert Bellekens]

I think you didn't have a good look at my second query.

The records of t_object with the alias "o" are the ones you are starting from.
The records of t_object with the alias "o2" are the ones with the stereotype that you are looking for, which is what I used in the second query.

The second query returns values from "o2" instead of "o", so those are the ones you are looking for.

In your query you are only returning the elements that have a relation from the start object, to the stereotyped object, but not if the relation goes (technically, not functionally) the other way.
That might be fine, but it is something to be aware of. Especially when dealing with associations this can be a gotcha.

Geert

Geert

[Hurra]

Thanks Geert, I implemented the union!

I have another question. It is easy to add a column with ParentID, by adding o.ParentID as Parent. However, I would like to have the name of the parent in the column. Is there a way to do this? I tried o.ParentID.Name but I guessed before I tried it it wouldn't work 

I guess it will be similar to your SQL searches here: https://bellekens.com/2011/01/14/harvesting-the-power-of-eas-sql-searches/ ?

Yup.

inner join t_object o2 on o.ParentID = o2.Object_ID

Works fine!

maybe I'm getting a hang of this

Thanks Geert!

[Hurra]

Hello again!

I added

Code: [Select]

inner join t_objectresource objr on o.Object_ID = objr.Object_ID

to fetch start and end date. It works fine but:

if the element have no resource allocation assigned the row comes empty.

Column 1: Name of related element
Column 2: Start date of column 1 element
Column 3: End date of column 1 element

So if column 1 element have no start/end date, there is no row. Can I somehow always show column 1, and leave column 2 and 3 blank if there is no data? Or do I have to do this with scripting?

Thanks!

[Geert Bellekens]

use "left join" instead of "inner join"

Geert

[Hurra]

use "left join" instead of "inner join"

Geert

Thanks for your fast reply Geert!

I really need to step up my SQL game.. hehe