User macro with CQL result

[Neta Elyakim]

Hey,

I'm trying to write user macro for confluence that run CQL search and the results will show in a table with columns.

I have written a user macro that gets all child pages form parent and divide then to columns

## @noparams
#set ( $descendantPages = $pageManager.getDescendents($content) )
#set( $urlbase = "http://test.com" )
<table>
<tr>
<th>Page name</th>
<th>Page URL</th>
<th>Created by</th>
<th>Last updated by</th>
<th>Last update date</th>
</tr>
 #foreach ( $page in $descendantPages )
 <tr>
 <td>$page.title</td>
 <td>$urlbase${req.contextPath}$page.urlPath</td>
 <td> $page.creator.name </td>
 <td> $content.getLastModifierName()</td>
<td>$action.dateFormatter.format($page.lastModificationDate)</td>
</tr>
 #end
</table>

I need that the CQL search will be -> cql=macroName:widget

Thanks!

[Stephen Deutsch · Accepted solution]

Hi Neta,

This is actually more difficult than you might expect from a user macro. I wrote something very hacky that piggybacks on the results of the "Content By Label" macro and is pretty slow, but maybe it will work for you:

## @param CQL:title=CQL Query|type=string|required=true|default=type = page and |desc=Enter CQL Query here, use pageId() to refer to current page ID and spaceKey() to refer to current space key.
#set ( $containerManagerClass = $content.class.forName('com.atlassian.spring.container.ContainerManager') )
#set ( $getInstanceMethod = $containerManagerClass.getDeclaredMethod('getInstance',null) )
#set ( $containerManager = $getInstanceMethod.invoke(null,null) )
#set ( $containerContext = $containerManager.containerContext )
#set ( $pageManager = $containerContext.getComponent('pageManager') )
#set ( $contentEntityManager = $containerContext.getComponent('contentEntityManager') )
#set ( $long = $generalUtil.getSystemStartupTime() )
#set ( $cql = $paramCQL.replace("pageId()", $content.id.toString()).replace("spaceKey()", $space.key) )
#set ( $htmlString = $action.getHelper().renderConfluenceMacro("{contentbylabel:showLabels=false|showSpace=false|max=500|cql=$cql}") )
#set ( $tableListings = "" )
#set ( $displayMatch = $req.contextPath + '/display/' )
#set ( $pagesMatch = $req.contextPath + '/pages/' )
#set ( $results = 0)
#foreach ( $linkPart in $htmlString.split('href="') )
 #if ( $linkPart.startsWith($displayMatch) || ($linkPart.startsWith($pagesMatch) && !$linkPart.contains('focusedCommentId=')) )
  #set ( $results = $results + 1 )
  #if ( $linkPart.startsWith($displayMatch) )
   #foreach ( $link in $linkPart.split('"') )
    #set ( $spaceAndPage = $link.replace($displayMatch, "" ) )
    #foreach ( $part in $spaceAndPage.split("/") )
     #set ( $spaceKey = $part )
     #break
    #end
    #foreach ( $part in $spaceAndPage.split("/") )
     #set ( $pageTitle = $generalUtil.urlDecode($part) )
    #end
    #set ( $page = $pageManager.getPage($spaceKey, $pageTitle) )
    #break
   #end
  #end
  #if ( $linkPart.startsWith($pagesMatch) )
   #foreach ( $link in $linkPart.split('"') )
    #foreach ( $section in $link.split('pageId=') )
     #set ( $pageId = $section )
    #end
    #break
   #end
   #set ( $pageId = $long.parseLong($pageId) )
   #set ( $page = $contentEntityManager.getById($pageId) )
  #end
  #set ( $tableListings = $tableListings + '<tr>' )
  #set ( $tableListings = $tableListings + '<td><a href="' + $req.contextPath + $page.urlPath + '">' + $page.title + '</a></td>')
  #set ( $tableListings = $tableListings + '<td>' + $page.creator.fullName + '</td>' )
  #set ( $tableListings = $tableListings + '<td>' + $action.dateFormatter.formatDateTime($page.creationDate) + '</td>' )
  #set ( $tableListings = $tableListings + '<td>' + $page.lastModifier.fullName + '</td>' )
  #set ( $tableListings = $tableListings + '<td>' + $action.dateFormatter.formatDateTime($page.lastModificationDate) + '</td>' )
  #set ( $tableListings = $tableListings + '</tr>' )
  #set ( $page = "" )
 #end
#end
#if ( $tableListings.contains("tr") )
 <p> Found $results results: </p>
 <table>
  <tr>
   <th>Page</th>
   <th>Created By</th>
   <th>Created Date</th>
   <th>Last Modified By</th>
   <th>Last Modified Date</th>
  </tr>
  $tableListings
 </table>
#else
 <p> No results found for CQL Query $cql </p>
#end

[Shankar Kalaga]

Hi @Stephen Deutsch , Can you please let me know how to add Labels column in the table data?  I was trying to insert table column but unable to get the label data in column. It would be a great help if you can give the code with label column.

[Shankar Kalaga]

Hi @Stephen Deutsch , Actually I used below code to search specific text in a table data.

I am able to pull the data in tabular format and able to search text, but I added a column to pull "Label" associated to pages in table. Below is the code I am using. I am unable to pull the label names in table.

## Macro Name: filter-all-tables
## Macro Title: Filter all tables
## Category: Confluence Content
## Macro has a body: N
## Body processing: None
##
## Source: https://community.atlassian.com/t5/Confluence-articles/The-Admin-s-tale-User-Macro-filtering-a-Confluence-table/ba-p/459369
## Date implemented: yyyy-mm-dd
## Implemented by: xxx.yyy@zzz.com

## @@param FilterID:title=FilterID|type=string|required=true|desc=ID [a-z,A-Z,0-9
## @param Label:title=Label|type=string|required=true|desc=Label
## @param Class:title=Length of the input field|type=enum|enumValues=short-field,medium-field,medium-long-field,long-field,full-width-field|default=long-field
## @param ColumnNumber:title=Column Number|type=string|required=true|default=-1|desc=Specify the column number or "-1" for the entire row

<form class="aui">
<input class="text $param0" type="text" id="searchInput" placeholder="Filter all tables (case insensitive)">
</form>

<script type="text/javascript">
AJS.$("#searchInput").keyup(function () {
var jqry = AJS.$
var rows = jqry("tr").hide();
var searchData = this.value;
if (searchData.length) {
var data = searchData.toLowerCase();
jqry.each(data, function (search_idx, str) {
rows.filter(function(row) {
return $(this).text().toLowerCase().indexOf(data) >= 0;
}).show();
});
} else rows.show();
AJS.$('thead tr').show();
});
</script>

##-----------

## @param CQL:title=CQL Query|type=string|required=true|default=type = page and |desc=Enter CQL Query here, use pageId() to refer to current page ID and spaceKey() to refer to current space key.
#set ( $containerManagerClass = $content.class.forName('com.atlassian.spring.container.ContainerManager') )
#set ( $getInstanceMethod = $containerManagerClass.getDeclaredMethod('getInstance',null) )
#set ( $containerManager = $getInstanceMethod.invoke(null,null) )
#set ( $containerContext = $containerManager.containerContext )
#set ( $pageManager = $containerContext.getComponent('pageManager') )
#set ( $contentEntityManager = $containerContext.getComponent('contentEntityManager') )
#set ( $labelManager = $containerContext.getComponent("labelManager"))
#set ($labelManager = $action.getLabelManger())
#set ($labelManager.getLabel($paramLabel))

#set ( $long = $generalUtil.getSystemStartupTime() )
#set ( $cql = $paramCQL.replace("pageId()", $content.id.toString()).replace("spaceKey()", $space.key) )
#set ( $htmlString = $action.getHelper().renderConfluenceMacro("{contentbylabel:showLabels=false|showSpace=false|max=500|cql=$cql}") )
#set ( $tableListings = "" )
#set ( $displayMatch = $req.contextPath + '/display/' )
#set ( $pagesMatch = $req.contextPath + '/pages/' )
#set ( $results = 0)
#foreach ( $linkPart in $htmlString.split('href="') )
#if ( $linkPart.startsWith($displayMatch) || ($linkPart.startsWith($pagesMatch) && !$linkPart.contains('focusedCommentId=')) )
#set ( $results = $results + 1 )
#if ( $linkPart.startsWith($displayMatch) )
#foreach ( $link in $linkPart.split('"') )
#set ( $spaceAndPage = $link.replace($displayMatch, "" ) )
#foreach ( $part in $spaceAndPage.split("/") )
#set ( $spaceKey = $part )
#break
#end
#foreach ( $part in $spaceAndPage.split("/") )
#set ( $pageTitle = $generalUtil.urlDecode($part) )
#end
#set ( $page = $pageManager.getPage($spaceKey, $pageTitle) )
#break
#end
#end
#if ( $linkPart.startsWith($pagesMatch) )
#foreach ( $link in $linkPart.split('"') )
#foreach ( $section in $link.split('pageId=') )
#set ( $pageId = $section )
#end
#break
#end
#set ( $pageId = $long.parseLong($pageId) )
#set ( $page = $contentEntityManager.getById($pageId) )
#end
#set ( $tableListings = $tableListings + '<tr>' )
#set ( $tableListings = $tableListings + '<td><a href="' + $req.contextPath + $page.urlPath + '">' + $page.title + '</a></td>')
#set ( $tableListings = $tableListings + '<td>' + $page.creator.fullName + '</td>' )
#set ( $tableListings = $tableListings + '<td>' + $page.labelname + '</td>' )

#set ( $tableListings = $tableListings + '<td>' + $action.dateFormatter.formatDateTime($page.creationDate) + '</td>' )
#set ( $tableListings = $tableListings + '<td>' + $page.lastModifier.fullName + '</td>' )
#set ( $tableListings = $tableListings + '<td>' + $action.dateFormatter.formatDateTime($page.lastModificationDate) + '</td>' )
#set ( $tableListings = $tableListings + '</tr>' )
#set ( $page = "" )
#end
#end
#if ( $tableListings.contains("tr") )
<p> Found $results results: </p>
<table>
<tr>
<th>Page</th>
<th>Created By</th>
<th>LabelName</th>
<th>Created Date</th>
<th>Last Modified By</th>
<th>Last Modified Date</th>
</tr>
$tableListings
</table>
#else
<p> No results found for CQL Query $cql </p>
#end

[Shankar Kalaga]

Hi @Stephen Deutsch , Can you please help?