SQL Query for custom field with multi enum

Description
gosst
Posts: 1
Joined: Mon Mar 04, 2019 6:17 am

SQL Query for custom field with multi enum

Postby gosst » Mon Mar 04, 2019 6:58 am

Hello,

currently I'm struggling with a SQL Query I'm using to filter my workitems based on custom fields.
It is based on that example described in SQLQueryExamples.pdf

My custom field is of type enum and is configured as multi enum. The issue is, that the query works as long as I'm using it with a enum limited to single value or in case of an multi enum if I select only a single value.
As soon as I select more than one value it uses only first or last value.

I'm using it for some sort of dynamic traceability report controlled by a page parameters to select testEnvironment and variant. The query get's build dynamically by macros based on that parameters and the resulting query looks like this f.e.

Code: Select all

select WORKITEM.C_URI
from WORKITEM
inner join PROJECT on PROJECT.C_URI = WORKITEM.FK_URI_PROJECT
where true
and PROJECT.C_ID = 'XYZ'
   and ( C_SEVERITY != 'will'
   or C_STATUS != 'rejected' )
    and C_TYPE = 'l1_requirement'
   and WORKITEM.C_PK in
   ( select CF2.FK_WORKITEM 
      from CF_WORKITEM CF2
       where (
         (  CF2.C_STRING_VALUE LIKE '%Env1%' 
          or CF2.C_STRING_VALUE LIKE '%Env2%'
          or CF2.C_STRING_VALUE LIKE '%Env3%'
          ) 
         and CF2.C_NAME = 'testEnvironment' 
)    )   
   and WORKITEM.C_PK in
   ( select CF1.FK_WORKITEM
      from CF_WORKITEM CF1
      where (
         (  CF1.C_STRING_VALUE LIKE '%var1%' 
          or CF1.C_STRING_VALUE LIKE '%var2%'
          or CF1.C_STRING_VALUE LIKE '%var3%'
          ) 
         and CF1.C_NAME = 'variant' 
)    )   
    and not exists (select * from WORKITEM TEST, STRUCT_WORKITEM_LINKEDWORKITEMS LINK
    where LINK.FK_WORKITEM = WORKITEM.C_PK
       and LINK.FK_P_WORKITEM = TEST.C_PK
       and LINK.C_ROLE = 'verifies'
)


(don't bother about the subqueries for now, I've already tried another version where I resolved them to JOINS but the behaviour is still the same, I might change that later for performance optimization)

Unfortunately I don't have access directly to the database so I can't see the stored data itself. So, I know that the custom field object does hold a list of java objects from type enum option from API. But I don't know how this does exactly map with VALUE fields in database schema.

And as long as I select only a single value it seems that I can compare these objects directly with STRING_VALUE field, so I guess there is an implicit cast. But with multiple values it seems that only the first value is used then.

I've also tried something like

Code: Select all

 CAST(CF1.C_STRING_VALUE as varchar) LIKE '%var1%' 

or

Code: Select all

 CAST(CF1.C_STRING_VALUE as text) LIKE '%var1%' 

but this doesn't change the result either.

As I can't look into database I can only guess that STRING_VALUE does always only contain the first value in case of multiselection? But how can I get the other values?
Or am I completely wrong with my approach for multi-values?

Do you have any ideas or an better approach?

Thank you in advance
Thomas

sergeD
Posts: 23
Joined: Tue Feb 05, 2013 9:24 am

Re: SQL Query for custom field with multi enum

Postby sergeD » Tue Mar 05, 2019 8:36 am

Hi
I didn t check your full query but i would suggest to use IN Statement ( https://www.w3schools.com/sql/sql_in.asp)
#set ($searchEnvt="'envt1,envt2,envt3'")
..
CF2.C_STRING_VALUE IN $searchEnvt
Sergio
PS: And if possible, install a SQLClient ( like HeidiSQL) , connect to the db, to write and test your sql statement outside Polarion

ahh
Posts: 9
Joined: Thu Jan 24, 2019 4:34 pm

Re: SQL Query for custom field with multi enum

Postby ahh » Fri Mar 08, 2019 9:08 pm

Hi Thomas,

For a multi enum field, each entry gets stored as it's own row in the cf_workitem table, so when you join workitem and cf_workitem, if a work item has multiple values for that custom field, each of those values will show up as a row in the resulting table, and you should be able to pick from there. For example, the following query:

select wi.c_id, cf.c_name, cf.c_string_value from polarion.workitem wi
inner join polarion.cf_workitem cf on wi.c_pk = cf.fk_workitem
where wi.c_type = 'condition' and cf.c_name = 'failureMode'

returns the following table:

wi.c_id cf.c_name cf.c_string_value
WI-19242 failureMode loss
WI-19245 failureMode malfunction
WI-19270 failureMode loss
WI-19270 failureMode malfunction

Notice that WI-19270 shows up twice, once for each entry in the failureMode multi enum field.

Hope this helps.

P.S. SquirrelSQL is a good client for this type of work that I've used in the past.

ruchika
Posts: 3
Joined: Mon Apr 15, 2019 8:19 am
Contact:

Re: SQL Query for custom field with multi enum

Postby ruchika » Mon Apr 15, 2019 8:32 am

For a multi enum field, every passage gets put away as it's own line in the cf_workitem table, so when you join workitem and cf_workitem, if a work thing has numerous qualities for that custom field, every one of those qualities will appear as a line in the subsequent table, and you ought to have the capacity to pick from that point. For instance, the accompanying question:

select wi.c_id, cf.c_name, cf.c_string_value from polarion.workitem wi

internal join polarion.cf_workitem cf on wi.c_pk = cf.fk_workitem

where wi.c_type = 'condition' and cf.c_name = 'failureMode'

restores the accompanying table:

wi.c_id cf.c_name cf.c_string_value

WI-19242 failureMode loss

WI-19245 failureMode malfunction

WI-19270 failureMode loss

WI-19270 failureMode malfunction

Notice that WI-19270 shows up twice, once for every section in the failureMode multi enum field.


Return to “Polarion Application Lifecycle Management (ALM)”

Who is online

Users browsing this forum: No registered users and 11 guests