Display

Phillip Riedel December 12, 2016

Hello,

 

I'd like to show kind of a "queue" showing queue positions with a pocket query mySQL statemant in confluence, using an order by clause.

What I do is:

  1. Join a few tables in a select statement
  2. select entries by different criteria
  3. sort them in an order by clause

In mySQL workbench I can use a custom variable to get a first column in the query result, showing the row number:

SET @qposition:=0;
SELECT 
	(@qposition:=@qposition+1) AS 'Position',

which does not work when using it in the pocket query.

Is there any other option to show the row number (position) of the result set?

Currently I use the following template:

<style>
.re { background: #F8E0E6; }
.yel { background: #F5F6CE; }
.gre { background: #CED8F6; }
</style>
## @param colresizable:true 
<table class="pocketquery-table confluenceTable">
  <tr>
  #foreach ($column in $columns)
    <th>$!column</th>
  #end
  </tr>
  #foreach ($row in $result)
    <tr class="
		##	#if($row.get("Tage seit letzter Aktualisierung") > 14)gre
		##	#end
			#if($row.get("Differenz zum Fertigstellungstermin") > 1 && $row.get("Differenz zum Fertigstellungstermin") < 3)yel
			#{elseif}($row.get("Differenz zum Fertigstellungstermin") >= 3)re
			#end">
    #foreach ($column in $row)
      <td>$!column</td>
    #end
    </tr>
  #end
</table>

I hope my question can be understood smile

Thanks an kind regards,

Phillip

1 answer

1 accepted

1 vote
Answer accepted
Carla Roocks (Scandio)
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 13, 2016

Hi Phillip,

Thank you for using PocketQuery! If I understand correctly, you want to simply number the rows of the resulting table?

You could do that directly within the template using a variable in a similar way, than you are currently doing in the SQL query. For example, the following template adds an additional column with a row number (starting from 1):

#set($number = 1)   ## define row counter

<table class="confluenceTable stickyTableHeaders"><tbody>
	<tr>
		<th class="confluenceTh">Row number</th>   ## add additional header cell
		#foreach ($column in $columns)
		<th class="confluenceTh">$!column</th>
		#end
	</tr>
	#foreach ($row in $result)
	<tr>
		<td class="confluenceTd">$number</td>   ## add additional cell with row counter
		#set($number = $number+1)    ## increment row counter

		#foreach ($column in $row)
		<td class="confluenceTd">$!column</td>
		#end
	</tr>
#end
</tbody>
<table>

Let me know, if this helps or if you have further questions!

Phillip Riedel December 13, 2016

Hi Carla,

many thanks for your immediate help. Works great and solved it for me.

BR Phillip

Felix Grund (Scandio)
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 13, 2016

In addition to Carla's answer: if you simply need a loop variable, you can also use the implicit ("magic") variable $velocityCount. It's available in any #foreach loop and starts with 1.

Carla Roocks (Scandio)
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 13, 2016

Good Point, Felix!

Phillip, I'm glad I could help. If you're happy with PocketQuery, maybe leave a review at the marketplace? We would highly appreciate it.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events