{"id":636,"date":"2014-08-01T18:28:26","date_gmt":"2014-08-02T01:28:26","guid":{"rendered":"https:\/\/blogs.plm.automation.siemens.com\/t5\/Polarion-Blog\/Tech-Tips-amp-Tricks-Querying-across-Documents-for-unlinked-Work\/ba-p\/380866"},"modified":"2026-03-26T05:29:29","modified_gmt":"2026-03-26T09:29:29","slug":"tech-tips-tricks-querying-across-documents-for-unlinked-work-items","status":"publish","type":"post","link":"https:\/\/blogs.sw.siemens.com\/polarion\/tech-tips-tricks-querying-across-documents-for-unlinked-work-items\/","title":{"rendered":"Tech Tips & Tricks: Querying across Documents for unlinked Work Items"},"content":{"rendered":"

Here’s another 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 query to discover unlinked Work Items across Polarion LiveDocs.
\n
\n

Use Case<\/H2>
\nTwo Documents within a project having linked Work Items with related to link role, like this:
\n
Doc1
\nWI-1, related to WI-3
\nWI-2, related to WI-7 <\/p>\n
Doc2
\nWI-3, related to WI-1, parent of WI-4
\nWI-4, has parent WI-3, related to Wi-6
\nWI-5, no link to Doc1, parent of WI-6
\nWI-6, has parent WI-5, related to WI-4
\nWI-7, related to WI-2<\/PRE>
\nYou want to find all Work Items on the parent level in Doc2 that are NOT linked to a Work Item in Doc1. This means that the query must return WI-5, and NOT WI-4 or WI-6, as they are linked within Doc2 to another item in Doc2.
\n
\"Query<\/A><\/P><\/p>\n
Solution<\/H2>
\nOnce again we’re transported from the realm of Lucene to the realm of SQL, making use of the background SQL database in Polarion that mirrors the Subversion repository data, and enables speedy complex queries that Lucene either can handle, or would have to be done in such a way as to bog down the system while the query is processed. Here is some example Wiki mark-up and code that provides a solution to our use case.
\n
#set($projectId = $page.getProject())<\/p>\n
1 Items from Doc2 without parent requirement in Doc2<\/p>\n
#set($my1Query = “SELECT item.C_PK FROM WORKITEM item, PROJECT proj, MODULE doc2 =>
\nWHERE proj.C_ID = ‘${projectId}’ AND doc2.C_ID = ‘Doc2’ AND doc2.C_MODULEFOLDER = =>
\n‘My_Space’ AND doc2.FK_URI_PROJECT = proj.C_URI AND item.C_TYPE = ‘requirement’ AND =>
\nitem.FK_URI_MODULE = doc2.C_URI AND NOT EXISTS (SELECT link.* FROM =>
\nSTRUCT_WORKITEM_LINKEDWORKITEMS link, WORKITEM parent WHERE link.C_ROLE = ‘parent’ =>
\nAND link.FK_URI_P_WORKITEM = item.C_URI AND link.FK_URI_WORKITEM = parent.C_URI =>
\nAND parent.C_TYPE = ‘requirement’ AND parent.FK_URI_MODULE = doc2.C_URI)”)<\/p>\n
{workitems:sqlQuery=$my1Query}<\/p>\n
1 Items from Doc2 not linked to Doc1<\/p>\n
#set($my2Query = “SELECT item.C_PK FROM WORKITEM item, PROJECT proj, MODULE doc2 =>
\nWHERE proj.C_ID = ‘${projectId}’ AND doc2.C_ID = ‘Doc2’ AND doc2.C_MODULEFOLDER = =>
\n‘My_Space’ AND doc2.FK_URI_PROJECT = proj.C_URI AND item.C_TYPE = ‘requirement’ =>
\nAND item.FK_URI_MODULE = doc2.C_URI AND NOT EXISTS (SELECT link.* FROM =>
\nSTRUCT_WORKITEM_LINKEDWORKITEMS link, WORKITEM neighbour, MODULE doc1 WHERE =>
\nlink.FK_URI_P_WORKITEM = item.C_URI AND link.FK_URI_WORKITEM = neighbour.C_URI AND =>
\nneighbour.FK_URI_MODULE = doc1.C_URI AND doc1.C_ID = ‘Doc1’ AND =>
\ndoc1.C_MODULEFOLDER = ‘My_Space’ AND doc1.FK_URI_PROJECT = proj.C_URI)”)<\/p>\n
{workitems:sqlQuery=$my2Query}<\/p>\n
1 The complete solution<\/p>\n
#set($my3Query = “SELECT item.C_PK FROM WORKITEM item, PROJECT proj, MODULE doc2 =>
\nWHERE proj.C_ID = ‘${projectId}’ AND doc2.C_ID = ‘Doc2’ AND doc2.C_MODULEFOLDER = =>
\n‘My_Space’ AND doc2.FK_URI_PROJECT = proj.C_URI AND item.C_TYPE = ‘requirement’ =>
\nAND item.FK_URI_MODULE = doc2.C_URI AND NOT EXISTS (SELECT linked.C_PK FROM =>
\nSTRUCT_WORKITEM_LINKEDWORKITEMS link, WORKITEM linked WHERE link.C_ROLE = ‘parent’ =>
\nAND link.FK_URI_P_WORKITEM = item.C_URI AND link.FK_URI_WORKITEM = linked.C_URI AND =>
\nlinked.C_TYPE = ‘requirement’ AND linked.FK_URI_MODULE = doc2.C_URI UNION SELECT =>
\nlinked.C_PK FROM STRUCT_WORKITEM_LINKEDWORKITEMS link, WORKITEM linked, MODULE doc1 =>
\nWHERE link.FK_URI_P_WORKITEM = item.C_URI AND link.FK_URI_WORKITEM = linked.C_URI =>
\nAND linked.FK_URI_MODULE = doc1.C_URI AND doc1.C_ID = ‘Doc1’ AND =>
\ndoc1.C_MODULEFOLDER = ‘My_Space’ AND doc1.FK_URI_PROJECT = proj.C_URI)”)<\/p>\n
{workitems:sqlQuery=$my3Query}<\/PRE>
\n
=> indicates continuation on the same line.<\/P><\/p>\n
<\/p>\n
Jiri Jandl is a Senior Support Engineer with Polarion Software’s European tech support team. He is based in Prague, Czech Republic.<\/EM><\/p>\n

\n
\"Banner:<\/A><\/P>
\n
<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Here’s another 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 mi…<\/p>\n","protected":false},"author":54129,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"spanish_translation":"","french_translation":"","german_translation":"","italian_translation":"","polish_translation":"","japanese_translation":"","chinese_translation":"","footnotes":""},"categories":[1],"tags":[],"industry":[],"product":[],"coauthors":[],"class_list":["post-636","post","type-post","status-publish","format-standard","hentry","category-news"],"_links":{"self":[{"href":"https:\/\/blogs.sw.siemens.com\/polarion\/wp-json\/wp\/v2\/posts\/636","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.sw.siemens.com\/polarion\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.sw.siemens.com\/polarion\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.sw.siemens.com\/polarion\/wp-json\/wp\/v2\/users\/54129"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.sw.siemens.com\/polarion\/wp-json\/wp\/v2\/comments?post=636"}],"version-history":[{"count":1,"href":"https:\/\/blogs.sw.siemens.com\/polarion\/wp-json\/wp\/v2\/posts\/636\/revisions"}],"predecessor-version":[{"id":637,"href":"https:\/\/blogs.sw.siemens.com\/polarion\/wp-json\/wp\/v2\/posts\/636\/revisions\/637"}],"wp:attachment":[{"href":"https:\/\/blogs.sw.siemens.com\/polarion\/wp-json\/wp\/v2\/media?parent=636"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.sw.siemens.com\/polarion\/wp-json\/wp\/v2\/categories?post=636"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.sw.siemens.com\/polarion\/wp-json\/wp\/v2\/tags?post=636"},{"taxonomy":"industry","embeddable":true,"href":"https:\/\/blogs.sw.siemens.com\/polarion\/wp-json\/wp\/v2\/industry?post=636"},{"taxonomy":"product","embeddable":true,"href":"https:\/\/blogs.sw.siemens.com\/polarion\/wp-json\/wp\/v2\/product?post=636"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/blogs.sw.siemens.com\/polarion\/wp-json\/wp\/v2\/coauthors?post=636"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}