Compare Fix Version/s to Affects Version/s in JQL/Scriptrunner

[Corey Schulz]

Hi!

Using JQL I'd like to be able to perform a query sort of like this:

project in (project1, project2) AND affectedVersion != fixVersion

Of course, this query doesn't work. I'd like to know how to compare the Affects Version field to the Fix Version field in JQL. My company's got ScriptRunner, which from what I can gather is required to do this kind of query.

Is there a builtin method in ScriptRunner that will do this kind of comparison? I know that the:

issueFunction in expression(....)

won't do it. 

If not, what's the best way to go about making a custom issueFunction to compare these fields? I tried following these examples, but don't have much experience in Groovy unfortunately.

Thanks so much for your help!

[Corey Schulz · Accepted solution]

So, here's what worked for me. Your mileage may vary, of course.

Examples are hard to find for scriptrunner, so this took a bit.

Forgive the trash-tier spacing, it didn't want to paste correctly.

package com.onresolve.jira.groovy.jql

import com.atlassian.jira.user.ApplicationUser
import com.atlassian.jira.issue.Issue
import com.atlassian.jira.jql.query.QueryCreationContext
import com.atlassian.jira.jql.validator.NumberOfArgumentsValidator
import com.atlassian.jira.util.MessageSet
import com.atlassian.query.clause.TerminalClause
import com.atlassian.query.operand.FunctionOperand

import java.io.IOException
import java.text.SimpleDateFormat
import java.util.Calendar

import groovy.json.JsonSlurper
import groovy.util.logging.Log4j

import org.apache.commons.httpclient.UsernamePasswordCredentials
import org.apache.commons.httpclient.auth.BasicScheme
import org.apache.commons.io.IOUtils
import org.apache.http.HttpResponse
import org.apache.http.client.HttpClient
import org.apache.http.impl.client.DefaultHttpClient
import org.apache.http.client.methods.HttpGet
import org.apache.lucene.index.Term
import org.apache.lucene.search.BooleanClause
import org.apache.lucene.search.BooleanQuery
import org.apache.lucene.search.Query
import org.apache.lucene.search.TermQuery

import java.text.MessageFormat

@Log4j
class PullRequestStatus extends AbstractScriptedJqlFunction implements JqlQueryFunction {

@Override
String getDescription() {
"Get Issues By Pull Request Status"
}

@Override
MessageSet validate(ApplicationUser user, FunctionOperand operand, TerminalClause terminalClause) {
def messageSet = new NumberOfArgumentsValidator(1, 1, getI18n()).validate(operand)

if (messageSet.hasAnyErrors()) {
return messageSet
}

messageSet.addMessageSet(validateSubquery(user, operand.args[0]))
messageSet
}

@Override
List<Map> getArguments() {
[
[
description: "Subquery", 
optional: false,
]
]
}

@Override
String getFunctionName() {
"testPullRequestStatus"
}

@Override
Query getQuery(QueryCreationContext queryCreationContext, FunctionOperand operand, TerminalClause terminalClause) {

def booleanQuery = new BooleanQuery()
def issues = getIssues(operand.args[0], queryCreationContext.applicationUser)

issues.each {Issue issue -> 
def affectedVersions = issue.getAffectedVersions() 
def fixVersions = issue.getFixVersions()

if(affectedVersions.size() > 0 && fixVersions.size() > 0)
{
def affectVersion = affectedVersions[0].getName()
def fixVersion = fixVersions[0].getName()

if (affectVersion != fixVersion) 
{
booleanQuery.add(new TermQuery(new Term("issue_id", issue.id as String)), BooleanClause.Occur.SHOULD)
}
}

}
return booleanQuery
}

}