JQL JIRA query for Reporter field being equal to the value of another custom field Edited

Hey!

I need a query for Reporter field from project XYZ being equal to the value of another custom field from another project (let's call it simply Name). The trick is, I'm using another query to define a list of issues that have precise values of Name that I want (for example I'm using query project = "ABC"). Is that possible to construct such query using Scriptrunner, and if so, how would it look like?

 

project = "ABC" gives a list of isues that has custom field "Name". I need to construct a query that would list all issues from project XYZ where field "Reporter" is equal to whatever value field "Name" has in each of the tickets from project = "ABC" query.

 

2 answers

1 accepted

Accepted Answer
2 votes
Henning Tietgens Community Champion Aug 03, 2017

This is not possible with the build-in JQL scripts. But you can try this custom JQL script:

package com.onresolve.jira.groovy.jql

import com.atlassian.jira.JiraDataType
import com.atlassian.jira.JiraDataTypes
import com.atlassian.jira.bc.issue.search.SearchService
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.customfields.impl.MultiSelectCFType
import com.atlassian.jira.issue.customfields.impl.MultiUserCFType
import com.atlassian.jira.issue.customfields.impl.SelectCFType
import com.atlassian.jira.issue.customfields.impl.UserCFType
import com.atlassian.jira.issue.customfields.option.Option
import com.atlassian.jira.issue.fields.CustomField
import com.atlassian.jira.jql.operand.QueryLiteral
import com.atlassian.jira.jql.query.QueryCreationContext
import com.atlassian.jira.jql.validator.NumberOfArgumentsValidator
import com.atlassian.jira.user.ApplicationUser
import com.atlassian.jira.util.MessageSet
import com.atlassian.jira.web.bean.PagerFilter
import com.atlassian.query.clause.TerminalClause
import com.atlassian.query.operand.FunctionOperand
import groovy.util.logging.Log4j

@Log4j
class GetUserFromField extends AbstractScriptedJqlFunction implements JqlValuesFunction {

static CustomField getCustomFieldFromText(String text) {
if (!text) return null
def customFieldManager = ComponentAccessor.customFieldManager
def cf = customFieldManager.getCustomFieldObject(text)
if (!cf) {
cf = customFieldManager.getCustomFieldObjectsByName(text).find{it.untranslatedName == text}
}
cf
}

static String getUserNameFromText(String text) {
if (!text) return null
def userManager = ComponentAccessor.userManager
def user = userManager.getUserByKey(text)
if (!user) {
user = userManager.getUserByName(text)
}
user?.name
}

@Override
MessageSet validate(ApplicationUser user, FunctionOperand operand, TerminalClause terminalClause) {
def messageSet = super.validate(user, operand, terminalClause)
messageSet.addMessageSet(new NumberOfArgumentsValidator(2, 2, getI18n()).validate(operand))
if (messageSet.hasAnyErrors()) {
return messageSet
}
def jqlQuery = operand.args[0]
def customFieldText = operand.args[1]

// Validate Query
def searchService = ComponentAccessor.getComponent(SearchService)
messageSet.addMessageSet(searchService.parseQuery(user, jqlQuery).errors)

// Validate customfield text
if (!getCustomFieldFromText(customFieldText)) {
messageSet.addErrorMessage("Could not find customfield $customFieldText. Please use the customfield id (like \"customfield_12345\") or the untranslated custom field name.")
}
messageSet
}

@Override
List<QueryLiteral> getValues(QueryCreationContext queryCreationContext, FunctionOperand operand, TerminalClause terminalClause) {
def searchService = ComponentAccessor.getComponent(SearchService)

Collection<String> userNames = []
if (operand.args?.size()==2) {
def jqlQuery = operand.args[0]
def customFieldText = operand.args[1]

// Execute Query
def issues = []
SearchService.ParseResult parseResult = searchService.parseQuery(queryCreationContext.applicationUser, jqlQuery)
if (parseResult.isValid()) {
def searchResult = searchService.search(queryCreationContext.applicationUser, parseResult.getQuery(), PagerFilter.getUnlimitedFilter())

// Collect users
def cf = getCustomFieldFromText(customFieldText)
def cfType = cf.getCustomFieldType()
if (cf) {
searchResult.issues.each { issue ->
def value = issue.getCustomFieldValue(cf)
if (value) {
switch (cfType) {
case {cfType instanceof UserCFType}:
userNames.add((value as ApplicationUser)?.name)
break
case {cfType instanceof MultiUserCFType}:
userNames.addAll((value as Collection<ApplicationUser>).name)
break
case {cfType instanceof SelectCFType}:
userNames.add(getUserNameFromText((value as Option).value))
break
case {cfType instanceof MultiSelectCFType}:
userNames.addAll((value as Collection<Option>).collect{getUserNameFromText(it.value)})
break
default:
userNames.add(getUserNameFromText(value as String))
}
}
}
// remove empty and duplicate customfield results
userNames = userNames.unique() - (null as ApplicationUser)
}
}
}
return userNames.collect{new QueryLiteral(operand, it)}
}

@Override
Integer getMinimumNumberOfExpectedArguments() {
2
}

@Override
JiraDataType getDataType() {
JiraDataTypes.USER
}

@Override
String getDescription() {
"Returns all Users saved in a customfield (id or untranslated name) from the result of a query."
}

@Override
List<Map> getArguments() {
[
["description": "JQL search", "optional": false],
["description": "customfield (id or untranslated name)", "optional": false],
]
}

@Override
boolean isList() {
true
}

@Override
String getFunctionName() {
"getUserFromField"
}
}

Copy this to the directory com/onresolve/jira/groovy/jql/ in your script root and scan for new JQL scripts on the JQL scripts page. After that you can use

project = XYZ and reporter in getUserFromField("project = ABC", Name)

to find all issues in XYZ with reporters in the field Name of all issues in project ABC. 

Best,
Henning

Thanks @Henning Tietgens for sharing this insighful code.

Quick query - You have implemented the interface JqlValuesFunction. Can you tell me the difference between the 3

1) JqlValuesFunction , 2) JqlFunction, 3) JqlQueryFunction

From the docs it's not clear, thus I was curious regarding when to use which one of the 3.

Henning Tietgens Community Champion Aug 03, 2017

IMO JqlFunction is not used anymore (correct @Jamie Echlin [Adaptavist]?). The rest is documented:

"implement com.onresolve.jira.groovy.jql.JqlQueryFunction for functions that utilise issueFunction (and have a getQuery() method)". This uses "issueFunction" and for the end user returns a list of issues. Your code has to return a lucene query object.

"implement com.onresolve.jira.groovy.jql.JqlValuesFunction for functions that return a list of QueryLiterals from the getValues() method." This is used to query for anything else, eg. users (like my code above), projects, components, where you return a list of pointers to these objects.

There are references to JqlFunction in the documentation, but I think they have to be replaced by JqlValuesFunction.

Henning

Thanks for the reply Henning!

Much obliged.

@Henning Tietgens

I would like to compare two custom fields of same type(i.e, 2 text fields or 2 select list).. I am looking for a custom JQL script of the same

Henning Tietgens Community Champion Dec 06, 2017

No, I haven't done that yet. For numbers, dates etc. you should take a look at ScriptRunners JQL calculations (https://scriptrunner.adaptavist.com/latest/jira/jql-functions.html#_calculations)

Henning

Thank you.
I am looking for comparision of same data type fields like(select list,multiselect etc)

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Thursday in Marketplace Apps

You + one app + a desert island...

Hi all! My name is Miles and I work on the Marketplace team. We’re looking for better ways to recommend and suggest apps that are truly crowd favorites, so of course we wanted to poll the Community. ...

346 views 3 5
Join discussion

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