Help with JqlQueryBuilder

[Charlie Misonne]

Dear Atlassian Community,

I'm trying to build a custom JQL function with scriptrunner.

We have a text field which contains IDs from thousands of issues. I need to check if this ID matches the numerical portion of the issue key.

An example:
issue key: ABC-123
myCustomField: ABC_00123

So I need to match issue.number with the part after _00 in the customfield.
I already have a working custom JQL function by implementing JqlValuesFunction.
But it is very slow so I want to do it with Lucence by implementing JqlQueryFunction.

Here is the queryBuilder part of my code:

def queryBuilder = JqlQueryBuilder.newBuilder()
def whereClause = queryBuilder
    .where()
    .customField(remedyIdCF.idAsLong)
    .like(<issue.number>)
    .endWhere()
    .buildQuery()
    .whereClause

But how can I reference the issue.number (or issue.key) from in a querybuilder?

And how can I use just a portion of myCustomField to perform the match?

There are not many examples available on the web and javadoc is not so straight forward.

Any help or pointers are appreciated!

[Stefan Stadler]

Hi @Charlie Misonne 

I am not sure, if it is possible to get an issue key for such a query at all.

What is the actual use case you want to achieve?

Is it something like looking for a specific issue key and check if that is also part of the custom field in this issue? Such as "key=ABC-123 AND myCustomField ~ 123"?

Or is it more like finding all the issues where the numeric key value is also part of myCustomField of the same issue? 

Regards,

Stefan

[Charlie Misonne]

Hi Stefan,

Thanks for your answer.

We import items from an external system. And it is important that the custom field and the issue keys's numerical portion match.
The actual name of the custom field is External ID.

Here is the working code for the getValues method of a custom JQL function implementing  JqlValuesFunction.

the function will return issues where the numerical part of the issue keys and the external ID field do not match. I need to exclude the zeroes, _ and - so I'm using a regex to perform that check.

   @Override
   String getFunctionName() {
        "remedyIdMismatchesKey"
   }

    @Override

    List<QueryLiteral> getValues(

        QueryCreationContext queryCreationContext, FunctionOperand operand, TerminalClause terminalClause

    ) {
        def remedyIdCF = customFieldManager.getCustomFieldObjectsByName("Remedy ID").first()
        def splitRegex = "(?:-|_)0*"

        List<Issues> matchingIssues = []

        def queryResult = Issues.search("\"External ID\" is not empty")

        queryResult.findAll { issue ->

            if (issue.number.toString() != issue.getCustomFieldValue(externalIdCF).split(splitRegex).last()){

                return true

            }

        }.collect{

            new QueryLiteral(operand, it.id)

        }

    }

I'm using it as follow:

issue in remedyIdMismatchesKey()

It takes several minutes to execute this JQL because we have more 100k+ issues in the impacted projects. I already optimized it by excluding any issues where the External ID is empty.

You're probably right, I don't think it can be done with a lucence based custom JQL function since I need to access the issue key to perform the match.

[Stefan Stadler]

Hi @Charlie Misonne 

what about using a scripted field? This field would be able to be calculated at runtime and knows about the custom field value as well as the issue key. That way, you could also use it in any kind of JQL just as it would be with a normal custom field.

Or if you would like to stick to a less resource intensive but more complex solution, you could go with an automation or a listener and update a radio button custom field whenever the "Remedy ID" is changed and also on issue creation (not sure if that is possible). That way, the check would happen only if the field has a value or is updated and would reduce the overall calculation a lot.

Let me know, if you need more input on that, but it seems you might already know what I mean.

Hope this idea helps in solving this scenario :)

Regards,

Stefan