Author Topic: User defined search - element attributes  (Read 1468 times)

MarcinG

  • EA User
  • **
  • Posts: 38
  • Karma: +0/-0
    • View Profile
User defined search - element attributes
« on: February 01, 2022, 08:32:04 pm »
Hi,

I have some elements, for which I defined tagged values.  There might be : Element {Type=MyType, AnotherTag=MyTag} and Element2 {Type=MyType, AnotherTag=DifferentTag}.
Is there any simple way to either build a query, or use SQL query (without pivots etc), that would allow finding Element + AnotherTag, using Type as filter (or part of where clause) ?

Best regards
Marcin

qwerty

  • EA Guru
  • *****
  • Posts: 12893
  • Karma: +369/-296
  • I'm no guru at all
    • View Profile
Re: User defined search - element attributes
« Reply #1 on: February 01, 2022, 08:58:12 pm »
You have to JOIN t_object.object_id with the corresponding t_objectproperties.

q.

MarcinG

  • EA User
  • **
  • Posts: 38
  • Karma: +0/-0
    • View Profile
Re: User defined search - element attributes
« Reply #2 on: February 01, 2022, 09:44:23 pm »
Thank you.  I know I need to join those two.  But as it is 1:M , filtering by any value from t_objectproperties will automatically eliminate any other record which contains values I need.

Marcin

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 11937
  • Karma: +465/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: User defined search - element attributes
« Reply #3 on: February 01, 2022, 09:55:26 pm »
Thank you.  I know I need to join those two.  But as it is 1:M , filtering by any value from t_objectproperties will automatically eliminate any other record which contains values I need.

Marcin

You can do a left join. This will show the result of the tagged value if they exist, but also show the element if the tagged value doesn't exist.
Make sure you add all constraints to t_objectproperties in the join conditions and not in the where clause, otherwise you'll effectively make it an inner join again.

Code: [Select]
select o.name, tv.Value as tagValue
from t_object o
left join t_objectproperties tv on tv.Object_ID = o.Object_ID
and tv.Property = 'Tagname'

Geert

MarcinG

  • EA User
  • **
  • Posts: 38
  • Karma: +0/-0
    • View Profile
Re: User defined search - element attributes
« Reply #4 on: February 01, 2022, 11:34:23 pm »
Thank you Geert,

I think I must have been not very clear in my description ;-).  I did it like this :
Code: [Select]
SELECT distinct bi_object.ea_guid , bi_object.Object_ID, bi_object.Name, bi_object.Alias, t_objectproperties.Value
from
(
SELECT distinct t_object.ea_guid , t_object.Object_ID, t_object.Name, t_object.Alias, t_objectproperties.Value
from t_object,t_objectproperties
WHERE
t_objectproperties.Object_ID = t_object.Object_ID
and t_objectproperties.Value = 'MyType') as bi_object
join t_objectproperties on t_objectproperties.Object_ID = bi_object.Object_ID
WHERE t_objectproperties.Property = 'MyProperty'

it seems to be doing exactly what I need it to do :-)

Marcin

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 11937
  • Karma: +465/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: User defined search - element attributes
« Reply #5 on: February 02, 2022, 12:32:22 am »
It feels like you are making it way more complicated then needs to be.

Code: [Select]
select o.ea_guid, o.Object_ID, o.Name, o.Alias, tvmt.Value as MytypeValue, tvmp.Value as MyPropertyValue
from t_object o
left join t_objectproperties tvmt on tvmt.Object_ID = o.Object_ID
and tvmt.Value = 'MyType'
left join t_objectproperties tvmp on tvmp.Object_ID = o.Object_ID
and tvmp.Property = 'MyProperty'

Weird thing about your query is that you are testing that the value of the tagged value is 'MyType' rather than the Property, but maybe that is a typo?
Quote
t_objectproperties.Value = 'MyType'

Geert

MarcinG

  • EA User
  • **
  • Posts: 38
  • Karma: +0/-0
    • View Profile
Re: User defined search - element attributes
« Reply #6 on: February 02, 2022, 12:46:16 am »
No, that's not a typo - I am checking two different tags.  For one tag - all my objects for particular project will have the same value, so I am checking the value.  But then, for those objects, I need to get a value of another tag (property).

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 11937
  • Karma: +465/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: User defined search - element attributes
« Reply #7 on: February 02, 2022, 01:06:48 am »
No, that's not a typo - I am checking two different tags.  For one tag - all my objects for particular project will have the same value, so I am checking the value.  But then, for those objects, I need to get a value of another tag (property).
So for the one tag you don't care about the name, but only about the value?

If you are only interested in the value of the tagged value MyProperty in case there is a tagged value with with MyType, then you can add that to the join criteria

Code: [Select]
select o.ea_guid, o.Object_ID, o.Name, o.Alias, tvmp.Value as MyProperty
from t_object o
left join t_objectproperties tvmp on tvmp.Object_ID = o.Object_ID
and tvmp.Property = 'MyProperty'
and exists (select tvmt.ea_guid from t_objectproperties tvmt
where tvmt.Object_ID = o.Object_ID
and tvmt.Value = 'MyType')

Geert
« Last Edit: February 02, 2022, 01:08:26 am by Geert Bellekens »