It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Complex JQL Function

Akhil Jain Jan 25, 2018

I have requirement to find certain linked issues of a issue in particular project or all of JIRA based on criteria.

The requirement is too find all the tickets which have due date later than parent linked issue 

For Eg. 
              Issue Ticket(MM/dd/yyyy)                                          Linked Issues

       ABCDEF-15(Due Date- 09/01/2018) ---> KLMNOP-254 (07/31/2018)
                                                                 ---> KLMNOP-380 (06/30/2018)
                                                                 ---> KLMNOP-410 (09/30/2018)
                                                                 ---> KLMNOP-415 (09/30/2018)
                                                                 ---> TUVWXY-12484 (06/30/2018)
                                                                 ---> KLMNOP-385 (06/30/2018)
                                                                 ---> KLMNOP-271 (06/30/2018)
                                                                 ---> KLMNOP-273 (06/30/2018)
                                                                 ---> KLMNOP-336 (12/31/2021)

Final Result

 Issue Ticket                                               Linked Issues(dates after link due date)

ABCDEF-15(Due Date- 09/01/2018)         ---> KLMNOP-410 (09/30/2018)
                                                                  ---> KLMNOP-415 (09/30/2018)
                                                                  ---> KLMNOP-336 (12/31/2021)

 

I want the linked issue for ABCDEF-15, this is a specific type of ticket.

The example provided Example JQL Function is too simple and does not explain the JQL API.

Here is my sample code

import java.util.LinkedList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.atlassian.crowd.embedded.api.User;
import com.atlassian.jira.JiraDataType;
import com.atlassian.jira.JiraDataTypes;
import com.atlassian.jira.issue.CustomFieldManager;
import com.atlassian.jira.issue.IssueManager;
import com.atlassian.jira.issue.link.IssueLinkManager;
import com.atlassian.jira.jql.operand.QueryLiteral;
import com.atlassian.jira.jql.query.QueryCreationContext;
import com.atlassian.jira.plugin.jql.function.AbstractJqlFunction;
import com.atlassian.jira.user.ApplicationUser;
import com.atlassian.jira.util.MessageSet;
import com.atlassian.plugin.spring.scanner.annotation.component.Scanned;
import com.atlassian.plugin.spring.scanner.annotation.imports.ComponentImport;
import com.atlassian.query.clause.TerminalClause;
import com.atlassian.query.operand.FunctionOperand;

/**
* Echoes the the string passed in as an argument.
*/
@Scanned
public class AkhilTestJqlFunction extends AbstractJqlFunction
{
private static final Logger log = LoggerFactory.getLogger(AkhilTestJqlFunction.class);

@ComponentImport
private final IssueLinkManager issueLinkManager;

@ComponentImport
private final IssueManager issueManager;

@ComponentImport
private final CustomFieldManager customFieldManager;

public AkhilTestJqlFunction(IssueLinkManager issueLinkManager, IssueManager issueManager,
CustomFieldManager customFieldManager) {
super();
log.warn("warn- AkhilTestJqlFunction constructor got called");
log.debug("debug- AkhilTestJqlFunction constructor got called");
log.trace("trace- AkhilTestJqlFunction constructor got called");
this.issueLinkManager = issueLinkManager;
this.issueManager = issueManager;
this.customFieldManager = customFieldManager;
}

public MessageSet validate(User searcher, FunctionOperand operand, TerminalClause terminalClause)
{
return validateNumberOfArgs(operand, 1);
}

public List<QueryLiteral> getValues(QueryCreationContext queryCreationContext, FunctionOperand operand, TerminalClause terminalClause)
{
log.warn("warn- get values got called");
log.debug("debug- get values got called");
log.trace("trace- get values got called");
final List<QueryLiteral> literals = new LinkedList<QueryLiteral>();
/*final List<UserHistoryItem> projects = userProjectHistoryManager.getProjectHistoryWithoutPermissionChecks(queryCreationContext.getApplicationUser());

for (final UserHistoryItem userHistoryItem : projects) {
final String value = userHistoryItem.getEntityId();

try {
literals.add(new QueryLiteral(operand, Long.parseLong(value)));
} catch (NumberFormatException e) {
log.warn(String.format("User history returned a non numeric project IS '%s'.", value));
}
}*/

return null;
}

public int getMinimumNumberOfExpectedArguments()
{
return 0;
}

public JiraDataType getDataType()
{
return JiraDataTypes.ISSUE;
}

public MessageSet validate(ApplicationUser arg0, FunctionOperand operand, TerminalClause arg2) {
return validateNumberOfArgs(operand, 0);
}



}


The above code is still in progress code.

Please help by providing solution and pointing me in right direction.

1 answer

0 votes
Ignacio Pulgar Martín Community Leader Jan 26, 2018

Hi Akhil,

Do you have ScriptRunner plugin available on your Jira instance? This one would avoid the necessity for coding, as it provides many convenient JQL functions.

By the way, speaking of Issue Links, we better speak of outward and inward links, rather than parent-child relationship, unless you are specifically referring to:

  • a standard issue which is the parent of a sub-task
  • rarely, an Epic which is a parent of a standard issue. When using the word 'parent' to refer to an Epic, it is usually refereed to as a 'parent epic' instead.

Sample outward/inward link descriptions are:

  • blocks/is blocked by
  • causes/is caused by
  • relates to/relates to

Note those are not parent/child relationships, specially obvious in the 'relates to' case.

You can find the issue link types here:

<Jira URL>/secure/admin/ViewLinkTypes!default.jspa

If you provide us with more information about the outward/inward descriptions you would like to use, we might helps you further.

Regards.

Akhil Jain Jan 28, 2018

@Ignacio Pulgar Martín Yes you are right, these are inward and outward links.
Yes I have ScriptRunner plugin.

These are mostly blocks/is blocked by links. 1-2 of them are relates to/relates.

Q.1 How can we return result from Scriptrunner plugin?

Ignacio Pulgar Martín Community Leader Jan 29, 2018

Hi Akhil,

Provided that dateCompare compares just dates from the very same issue, groovy code is required.

Maybe @Alexey Matveev [cPrime] can help you further, as he is a distinguished Champion with widely proven scripting expertise.

Regards

Akhil Jain Jan 29, 2018 • edited

Thanks @Ignacio Pulgar Martín .
Waiting for response from @Alexey Matveev [cPrime]

Ignacio Pulgar Martín Community Leader Jan 29, 2018

Just for the record, please, kindly note that Alexey might be willing to help you, solely based on his own decision, as he does not have any obligations to solve, or even to answer to this question.

Community Champions contribute here just for fun.

So, we are in fact asking him for a favor :)

Best regards

Akhil Jain Jan 29, 2018 • edited Jan 30, 2018

Yes Thanks for all the help.

@Alexey Matveev [cPrime] Please help with the problem.

Alexey Matveev [cPrime] Community Leader Jan 30, 2018

Hello,

You would need to override getQuery function in Scriptrunner 

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

Inside the function you need to select all issues which you want to check (you should limit the number of the issues by your jql query or your function will take too much time to execute):

ApplicationUser user = ComponentAccessor.getJiraAuthenticationContext().getLoggedInUser() 
def booleanQuery = new BooleanQuery()
issueResults = searchService.search(user, subQuery, PagerFilter.getUnlimitedFilter())
List<Issue> issues = issueResults.getIssues()
for (Issue curIssue in issues) {
}

 

Then inside the cycle you should check every issue for the condition you need and if it satisfies your condition then you can add the issue to booleanQuery

booleanQuery.add(new TermQuery(new Term("issue_id", curIssue.id as String)), BooleanClause.Occur.SHOULD)
}

 

Then return booleanQuery

return booleanQuery;

Akhil Jain Jan 31, 2018 • edited

@Alexey Matveev [cPrime] Thanks for the help. It really cleared my doubt.

I have few more questions
Q.1 How would i override getQuery in ScriptRunner?
Q.2 In the code you shared, what exactly is subQuery and searchService?

issueResults = searchService.search(user, subQuery, PagerFilter.getUnlimitedFilter())

 Do I need to integrate the code you shared in Custom JQL Function?

Alexey Matveev [cPrime] Community Leader Jan 31, 2018

1. Kindly have a look at the following tutorial. It is how you overide it:

https://scriptrunner.adaptavist.com/latest/jira/custom-jql-functions.html

2. You can write something like this

subQuery = "poject = TEST"

it is a usual JQL query.

Akhil Jain Jan 31, 2018

will calling the JQL function automatically initialize subQuery?

Alexey Matveev [cPrime] Community Leader Jan 31, 2018

No, you need to define it in your script like

def subQuery = "poject = TEST"

Akhil Jain Jan 31, 2018

I want sub query to come from user search, if there is any..instead of me manually adding in JQL function

Alexey Matveev [cPrime] Community Leader Jan 31, 2018

You can pass a JQL string as a parameter and then use it. If you pass it as the first parameter you can get it by 

operand.args[0]

Akhil Jain Jan 31, 2018 • edited

How to concatenate for queries where 
project ="xyz" and issuetype="Defect" and customJqlFunction


will it internally manage all the issues from concentated query
Will this work fine??

Suggest an answer

Log in or Sign up to answer
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Published in Jira

Try Jira Cloud for Outlook: Organize your work without leaving your inbox

Hi Atlassian community, My name is Max and I work on the product integration team at Atlassian. I am pleased to announce the early access program for the Jira Cloud add-in for Outlook. This add-in...

2,179 views 6 15
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you