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

[Igor Volovsky]

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.

[Henning Tietgens · Accepted solution]

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

[Tarun Sapra]

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]

IMO JqlFunction is not used anymore (correct @JamieA?). 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

[Igor Volovsky]

Thanks for the reply Henning!

Much obliged.

[Vineela Durbha]

@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]

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

[Vineela Durbha]

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