Get Count from Dataset SQL query

Description
dbosman
Posts: 12
Joined: Wed Feb 08, 2017 10:16 am

Get Count from Dataset SQL query

Postby dbosman » Thu Nov 21, 2019 2:32 pm

I am busy building a SQL query using aggregates (GROUP BY). I want to count the number of results. I want to use the Dataset instance to execute the query. How do I retrieve the 'cnt' column from the result?


Code: Select all

#set($sql="SELECT COUNT(m.c_uri) as cnt FROM polarion.module m  JOIN polarion.project p ON m.FK_URI_PROJECT = p.c_uri  WHERE p.c_id LIKE 'XYZ'")

#set($result = $dataService.sqlSearch($sql))

#foreach($w in $result)

$w.cnt() <BR>

#end


This however does not work, no cnt is available.
Last edited by dbosman on Sun Nov 24, 2019 6:57 pm, edited 2 times in total.

Jürgen
Posts: 100
Joined: Tue Sep 12, 2017 1:02 pm

Re: Get Count from Dataset SQL query

Postby Jürgen » Fri Nov 22, 2019 1:34 pm

Hello, dbosman

I cannot tell you the right solution, as I cannot test it at the moment, but I can tell you things that look strange in your script.
1. behind the XYZ the single quotation is missing.
2. If the SQL query does what I expect, then it produces a table with a single column as a result, that contains numbers. I have never tried that, as the SQL query is typically used to return a set of work items.
3. If it works as expected, then the result should contain a list of IPObjects, because that is what the function sqlSearch delivers; That means, $w is also an IPObject. Thus you cannot call cnt() on it, but maybe getValue() or getCustomField. .

Jürgen

dbosman
Posts: 12
Joined: Wed Feb 08, 2017 10:16 am

Re: Get Count from Dataset SQL query

Postby dbosman » Sun Nov 24, 2019 7:23 pm

Hi, I modified the post and fixed the syntax error. Still no idea however to retrieve the count from an SQL query.

I am now trying to use a widget to get the count:

Code: Select all

<span class="polarion-rp-inline-widget" data-widget="com.polarion.countOfObjects">
  <span class="polarion-rp-widget-parameters">
   
    <sub id="dataSet">
     
      <sub id="prototype">WorkItem</sub>
     
      <sub id="queryType">sql</sub>
      <sub id="sqlColumnsQuery">
      </sub>
      <sub id="sqlTablesQuery">
JOIN MODULE ON WORKITEM.fk_module = MODULE.c_pk
     
      </sub>
         
      <sub id="sqlRestQuery">and PROJECT.C_ID = 'XYZ'
and MODULE.c_id = '$DOCUMENTNAME'
      </sub>
    </sub>
    <sub id="before"></sub><sub id="after"></sub>     
  </span>
</span>


I tried to add the following to the "sqlRestQuery" to filter on incomplete(i.e. not approved or status!=reviewed) items by using a Lucene query:

Code: Select all

and WORKITEM.c_pk in (select * from polarion.lucene_query('WorkItem', '(NOT approvalState:approved) OR (NOT status:reviewed)', 'id'))     


This seems to be really slow.

Looking at the database schema: https://almdemo.polarion.com/polarion/s ... Index.html
There is also an approvals table POLARION.STRUCT_WORKITEM_APPROVALS

So maybe its possible to do everything in SQL. To add the approvals info:

Code: Select all

JOIN STRUCT_WORKITEM_APPROVALS ON FK_P_WORKITEM = WORKITEM.c_pk


But I cannot find info on the STRUCT_WORKITEM_APPROVALS.C_STATUS field. How can i filter by "non approved" items?

Jürgen
Posts: 100
Joined: Tue Sep 12, 2017 1:02 pm

Re: Get Count from Dataset SQL query

Postby Jürgen » Mon Nov 25, 2019 9:38 am

You mean you do not know the exact values for the different states? Those are most probably the same values as you are using in the lucene query "approvalState:approved".

If that doesn't work then you could do a simple query on the approval table and get the distinct status values.

dbosman
Posts: 12
Joined: Wed Feb 08, 2017 10:16 am

Re: Get Count from Dataset SQL query

Postby dbosman » Tue Nov 26, 2019 8:37 am

Jürgen wrote:You mean you do not know the exact values for the different states? Those are most probably the same values as you are using in the lucene query "approvalState:approved".

If that doesn't work then you could do a simple query on the approval table and get the distinct status values.


Exactly.

How can I perform a custom raw SQL query via the Polarion GUI (I don't have access to the DB directly)? If I use the workitem tracker I can't select any custom columns (I must select a workitem id).

Jürgen
Posts: 100
Joined: Tue Sep 12, 2017 1:02 pm

Re: Get Count from Dataset SQL query

Postby Jürgen » Tue Nov 26, 2019 9:56 am

Maybe this is the right time to use a tool for direct database access. Maybe you should try SquirrelSQL together with Polarion. That tool allows to do any query on the database.

Polarion even supports this. The following link contains a PDF that tells you how to configure direct access:
https://almdemo.polarion.com/polarion/sdk/doc/database/ExternalDatabaseConnection.pdf

This is also very useful for testing queries without the Polarion GUI. And as you seem to do regular querying the effort should be worth it.

fbachmann
Posts: 54
Joined: Thu Apr 24, 2014 12:10 pm

Re: Get Count from Dataset SQL query

Postby fbachmann » Fri Jul 10, 2020 3:47 pm

Just wondering... why not use $result.size() once the SQL query has filled $result?


Return to “Polarion Application Lifecycle Management (ALM)”

Who is online

Users browsing this forum: No registered users and 14 guests