Display

[Phillip Riedel]

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 

Thanks an kind regards,

Phillip

[Carla Roocks (Scandio) · Accepted solution]

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]

Hi Carla,

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

BR Phillip

[Felix Grund (Scandio)]

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)]

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.