Tech Tips & Tricks: Find all Requirement Work Items that are not refined yet
Welcome to another installment in our series of tips and tricks from the Polarion Software Technical Support team, in which we share solutions we provided to customers for real-world use cases that. The same tips might help you too, or others on your team. Let’s look today at how to find “top-level” Requirement type Work Items that are not yet refined by linking to another Requirement with a refinement relationship.
Use Case
Customer question: “I want to find all Requirements that are not yet refined”. This means Requirement type Work Items that do not have any linked Work Items with link role “is refined by”.
Solution
You need to query for all Requirement items in a project (or in a Document), and then search the results again for something — a specific link role in this case. The problem is, that joined queries are not possible in Lucene, so you cannot find what you need in the Work Items table using the Query Builder. You might manage to construct a complex query with Lucene syntax, but system performance would take a hit. Such cases are the main reason we introduced the SQL database in Polarion. It quietly mirrors the SVN repository in the background, and when a complex join is needed, users can create queries with SQL to get the data they need fast and not bog down the system.
You can use the following example in a Wiki page. You use a SQL query to find all Requirements from a project, and among those find which ones are not refined by another Requirement (in the same project). Because the query uses SQL syntax, Polarion will not attempt to process it with Lucene, but will use the embedded SQL database. So… here goes.
#set($projectId = $page.getProject())#set($myQuery = “SELECT req.C_PK FROM WORKITEM req, PROJECT proj WHERE =>
proj.C_ID = ‘${projectId}’ AND req.C_TYPE = ‘requirement’ AND =>
req.FK_URI_PROJECT = proj.C_URI AND NOT EXISTS =>
(SELECT refining.C_PK FROM WORKITEM refining, STRUCT_WORKITEM_LINKEDWORKITEMS link =>
WHERE refining.C_TYPE = ‘requirement’ AND refining.FK_URI_PROJECT = proj.C_URI AND =>
link.FK_URI_WORKITEM = req.C_URI AND link.FK_URI_P_WORKITEM = refining.C_URI AND =>
link.C_ROLE = ‘refines’)”)* $myQuery
{workitems:sqlQuery=$myQuery}
=> indicates continuation on same line.
Jiri Jandl is a Senior Support Engineer with Polarion Software’s European tech support team. He is based in Prague, Czech Republic.
