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
Accepted answer

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!

Hi Carla,

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

BR Phillip

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.

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
Community showcase
Published Nov 29, 2018 in Marketplace Apps

How to set up an incident workflow from the VP of Engineering at Sentry

Hey Atlassian community, I help lead engineering at Sentry, an open-source error-tracking and monitoring tool that integrates with Jira. We started using Jira Software Cloud internally last year, a...

1,334 views 0 8
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you