Custom CQL Functions

Using custom CQL functions in ScriptRunner for Confluence allows you to create and share custom CQL functions (values) with your users in order to empower their search. For a large instance, some queries could include multiple CQL statements that could get complicated very fast. Creating this custom function would allow your users to use that and perform a basic CQL query. 

For help with CQL, visit Guide to CQL.

Create a custom CQL function

To create a custom CQL function, follow these steps: 

  1. Navigate to General Configuration > ScriptRunner > CQL Functions. 
  2. Select Create CQL Function. 
  3. Select Custom CQL Function.
  4. Fill out the following fields: 
    • Name: This is a mandatory field used as the CQL function display name. It should respect the list of reserved words and characters.
    • Note: This is an optional field, used only for your reference and not used internally. This is where you can describe what the function does.
    • Number of Parameters: This determines the number of parameters accepted by the CQL Function. For example, enter 1 if you want the user to provide only the space key.
    • Type: This specifies if the CQL Function returns a single string value or a list of strings. 
      • Single value: This type should return a single String value, which will be the value that the query function will be transformed into. This appears as a single quoted value following the operator in the CQL statement being executed.
        Example: = pagesWithLabel
      • Multi value: This type should return an Iterable of String values, which will be the values that the query function will be transformed into. This appears within the parenthesis following the IN clause in the CQL statement being executed.
        Example: = pagesWithLabel("finance")
    • Script file: If you have the script in a file, use this to enter the path accessible by the server.

    • Inline script: This is where we define what the CQL functions return based on the input.

      Binding Variables

      There are two binding variables available in the CQL Function script:

      • params : Parameters passed into the Query Function in the CQL statement being executed

      • context : The class definition of the object that is passed as the context argument to the invoke method of a Query Function

  5. Select Add to create the function. 

Result

The new custom CQL functions will appear here: 

And you can use them when performing CQL searches or using the CQL Search macro.

Custom CQL function example

Return attachments within a specified space

In this example, you'll create a simple CQL function that will return attachments within a specified space. To start, follow these steps: 

  1. Navigate to General Configuration > ScriptRunner > CQL Functions. 
  2. Select Create CQL Function. 
  3. Select Custom CQL Function.
  4. Enter spaceAttachements for Name. 
  5. Enter Returns attachments within a specified space for Note. 
  6. Enter 1 for Number of Parameters.
    Since we want the user to enter only the space key, the value is 1. 
  7. Pick Multi Value for Type. 
    We're using Multi Value because the function returns a list of attachments.

  8. Enter this script for Inline Script. 

    groovy
    import com.atlassian.confluence.pages.Attachment
import com.atlassian.confluence.pages.AttachmentManager
import com.atlassian.confluence.pages.Page
import com.atlassian.confluence.pages.PageManager
import com.atlassian.confluence.spaces.Space
import com.atlassian.confluence.spaces.SpaceManager
import com.atlassian.sal.api.component.ComponentLocator

def attachmentManager = ComponentLocator.getComponent(AttachmentManager)
def spaceManager = ComponentLocator.getComponent(SpaceManager)
def pageManager = ComponentLocator.getComponent(PageManager)
def names = []

String spacekey = params[0]
Space space = SpaceManager.getSpace(spaceKey)
for (Page page : pageManager.getPages(space, true)) { 
	for (Attachment attachment : attachmentManager.getLatestVersionsOfAttachments(page)) { 
		names << attachment.getFileName()
	}
}

return names
  9. Select Add to create the function.

Result: Once the function is created it’s possible to retrieve the output using the ScriptRunner macro CQL Search. Use the query title in spaceAttachments("ds") to retrieve the name of all attachments in the DS (Demonstration Space). You can replace "DS" with whatever your space is named.

Custom CQL function example with included code

The following examples use code that is included with ScriptRunner for Confluence. 

Search page by label

If you create this custom CQL function, you can return all pages that contain a specified label. To set up this CQL function, follow these steps: 

  1. Navigate to General Configuration > ScriptRunner > CQL Functions. 
  2. Select Create CQL Function. 
  3. Select Custom CQL Function.
  4. Enter pagesWithLabel for Name. 
  5. Enter Returns all pages with a certain label for Note. 
  6. Enter 1 for Number of Parameters. 
  7. Pick Multi Value for Type. 

  8. Select Show Snippets, and then choose Pages with a certain label. 

    Alternatively, here is the code for this custom CQL function: 

    An error occured

    There is a problem with the file path provided or a failure to connect with Bitbucket. Check the File Path provided, Application Link for Bitbucket Data Center or the permissions of the app password for Bitbucket Cloud. Contact your system administrator.

    Line 10: Getting the label input value

    Line 13: Iterating over all spaces

    Line 14: Iterating over all pages

    Line 15: Iterating over all labels

    Line 17: Adding the page ID if a label matches the input

  9. Select Add to create the function.

Result: Once the function is created, you can use it with the CQL Search macro. A CQL query that you could use with the function is content in pagesWithLabel("finance") to retrieve all pages with a label in a Finance space, for example. You can replace "Finance" with whatever your space is named. 

Return all pages using a specific macro

If you create this custom CQL function, you can return all the pages that are using any macro of a specified add-on. To set up this CQL function, follow these steps: 

  1. Navigate to General Configuration > ScriptRunner > CQL Functions. 
  2. Select Create CQL Function. 
  3. Select Custom CQL Function.
  4. Enter addOnPages for Name. 
  5. Enter Return all add-on pages for Note. 
  6. Enter 1 for Number of Parameters. 
  7. Pick Multi Value for Type. 

  8. Select Show Snippets, and then choose Pages containing macros from a certain add-on. 

    Alternatively, here is the code for this custom CQL function:

    An error occured

    There is a problem with the file path provided or a failure to connect with Bitbucket. Check the File Path provided, Application Link for Bitbucket Data Center or the permissions of the app password for Bitbucket Cloud. Contact your system administrator.

    Line 29: Getting the plugin associated with the key specified

    Line 32: Retrieving all the macros associated with the plugin

    Line 34: Getting all the pages that are using at least one plugin macro

    Line 37: Iterating over the pages to return the list of IDs

  9. Select Add to create the function.

Result: Once the function is created, you can use it with the CQL Search macro. A CQL query that you could use with the function is content in addOnPages("com.adaptavist.confluence.formMailNG").

The function retrieves the add-on macros defined in the plugin descriptor.

On this page