Problem: CQL query returns 1 result, but results is empty when iterating

[Alex]

I'm using a CQL query to search for pages with comments created within the last 7 days in a specific space. The query seems to return 1 result based on the log message, but when I attempt to iterate over the results using results.each, the results appears empty.

import com.atlassian.confluence.pages.AbstractPage

import com.onresolve.scriptrunner.canned.confluence.utils.CQLSearch

import com.atlassian.confluence.search.v2.SearchManager

import com.onresolve.scriptrunner.canned.confluence.utils.CQLSearchUtils

import com.onresolve.scriptrunner.runner.ScriptRunnerImpl

import com.atlassian.sal.api.component.ComponentLocator

import com.atlassian.confluence.pages.Page;

def cqlSearchUtils = ScriptRunnerImpl.scriptRunner.getBean(CQLSearchUtils)

String cqlQuery = 'space = INFO and type=comment and created >= now("-7d")'

def cqlSearch = new CQLSearch()

Iterable<AbstractPage> results = cqlSearchUtils.searchForPages(CQLSearch.fromQuery(cqlQuery))

log.warn("Find pages : ${results.size()}")

results.each { result ->

log.warn("Page: ${result.title}, ID: ${result.id}")

}

[Mia Tamm _Simpleasyty_]

Hi @Alex,

I ran into the same issue recently when trying to report on recent comments. Two things are at play here:

• Iterating a consumable result set – The searchForPages() method returns an Iterable of search results. Calling size() on that iterable consumes the underlying iterator, so by the time you call each there is nothing left to traverse. The Confluence API docs show that SearchResults implements Iterable and provides a size() method to get the number of returned results and a getAll() method to produce a list. If you call size() first, you’ve already iterated through the results. To avoid this, convert the results to a list (or use getAll()) and then work with that list:

def results = cqlSearchUtils.searchForPages(CQLSearch.fromQuery(cqlQuery)).toList()

log.warn("Find pages: ${results.size()}")

results.each { page ->
    log.warn("Page: ${page.title}, ID: ${page.id}")
}

This way the result set is realised once, and you can safely get its size and iterate over it.

• Querying for comments vs pages – Your CQL uses type = comment, so the search returns comment objects. However, searchForPages() expects pages and blog posts, which can lead to unexpected behaviour. A better approach is to use the CQLSearchService.searchContent() API to run your query and get back a PageResponse<Content> object. This API lets you search across any content type and then inspect each returned Content item. Here’s a working example adapted from the ScriptRunner documentation:

import com.atlassian.confluence.api.model.Expansion
import com.atlassian.confluence.api.model.content.Content
import com.atlassian.confluence.api.model.pagination.SimplePageRequest
import com.atlassian.confluence.api.model.search.SearchContext
import com.atlassian.confluence.api.service.search.CQLSearchService
import com.onresolve.scriptrunner.runner.ScriptRunnerImpl

def cqlSearchService = ScriptRunnerImpl.getOsgiService(CQLSearchService)
def cqlQuery = 'space = INFO and type = comment and created >= now("-7d")'
def pageRequest = new SimplePageRequest(0, 50)
def searchResult = cqlSearchService.searchContent(
    cqlQuery,
    SearchContext.builder().build(),
    pageRequest,
    Expansion.combine("container") // get parent page data
)

searchResult.each { Content comment ->
    def parentPage = comment.container
    log.warn("Comment ${comment.id} created ${comment.history.createdDate} on page ${parentPage?.title} (${parentPage?.id})")
}

This script searches for comments created in the last seven days and then logs the ID and creation date of each comment along with the title and ID of its parent page.

With these adjustments, you should see your results logged correctly.

— Mia Tamm from Simpleasyty