SQL Workitem query for a certain attribute (custom enum)

Description
bleyl
Posts: 1
Joined: Mon Feb 26, 2018 5:31 pm

SQL Workitem query for a certain attribute (custom enum)

Postby bleyl » Mon Feb 26, 2018 7:00 pm

I would like to find a set of workitems using a SQL Query:
$myItems = $dataService.sqlSearch( ....)

I would like to find all workitems with a certain attribute (custom enum) value.

Assuming, I manage "cars".
Cars have a "frontColor" which is described as a custom enum with three possible values:
"red" (ID = 1), "green" (ID = 2), "blue" (ID =3)

To find all "blue" cars, I am using:

"select REQ.C_PK from WORKITEM REQ inner join CF_WORKITEM on CF_WORKITEM.FK_WORKITEM = REQ.C_PK where REQ.C_TYPE = 'car' and CF_WORKITEM.C_NAME ='frontColor 'and CF_WORKITEM.C_STRING_VALUE='3' ")

This works fine.
Note that I am using the ID for color "blue", which is 3, but not the String (Value) "blue".

Now, the question is: How to change the query in a way that I can really ask for the color value "blue"?

Thanks

ALMighty
Posts: 80
Joined: Wed Apr 27, 2016 10:34 am
Location: Austria

Re: SQL Workitem query for a certain attribute (custom enum)

Postby ALMighty » Wed Feb 28, 2018 12:32 pm

bleyl wrote:Assuming, I manage "cars".
Cars have a "frontColor" which is described as a custom enum with three possible values:
"red" (ID = 1), "green" (ID = 2), "blue" (ID =3)


You are aware that you could change the enumeration frontColor inclduing the ID of each color (provided that you are the admin your project) in the administration section? Instead of 3, the color blue could not just have the name blue but also the id blue.

bleyl wrote:To find all "blue" cars, I am using:

"select REQ.C_PK from WORKITEM REQ inner join CF_WORKITEM on CF_WORKITEM.FK_WORKITEM = REQ.C_PK where REQ.C_TYPE = 'car' and CF_WORKITEM.C_NAME ='frontColor 'and CF_WORKITEM.C_STRING_VALUE='3' ")



To be honest I have no solution for this. My first guess was Joining CF_WORKITEM with the table enum_options, which has the column c_enumid (which would be frontColor in your case). However enum_options is obviously empty. Neither in a wikipage nor in my PostgreSQL (pgAdmin 3) I get a resultset with more than 0 rows but maybe that error is just on my side.

Yuo can try it out by yourself, here is the query I executed in pgAdmin 3:

Code: Select all

SELECT * FROM polarion.enum_options;


And here is the code snippet for a wikipage in which I executed the query:

Code: Select all

#set($projectID = "$page.space.projectId")
#set($dataService = $trackerService.getDataService())
#info("This page shows an example of how to fetch objects (in this case work enum options) with SQL queries in a wiki page")

#set($sampleObjectList = $dataService.sqlSearch("SELECT * FROM polarion.enum_options"))
#foreach($tpObject in $sampleObjectList)
URI of the object: $tpObject.getUri() <br />
#end ## end foreach



If you want to know more abouzt executing SQL queries in a wikipage, check here my answer in the thread SQL query example for workitems in a plan.

By the way, if you have not seen the database documentation of Polarion, here it is, especially the Full DB schema can be a good help: https://almdemo.polarion.com/polarion/s ... index.html
---------------------------------------------------------------------------------------------------
Playing a good game is never lame. :D
Image


Return to “Polarion Application Lifecycle Management (ALM)”

Who is online

Users browsing this forum: No registered users and 3 guests