To be able to return all issues of a particular type and their respective children?

[Emanuel Paquetelima]

Hello guys,
Can anyone tell me how I can make a JQL that returns all parent issues of a given type in a given state and their respective children?

Can you help me?

Thank you in advance

[Iago Docando]

I don't think this is possible with vanilla Jira. A couple options that come to mind:

• If you use Script Runner (paid plugin) you could search for:

(issuetype = XXX AND status = YYY) OR issueFunction in subtasksOf("issuetype = XXX AND status = YYY")

It is a direct solution. Maybe not worth it if you only want to do this particular search but with script runner you can do a lot of things. In any case you should check it out.

• If you use MyGroovy Script (free plugin):

You can create a custom JQL function to do exactly that. I feel this is a fairly needed functionality.

To use the custom function search for

issue in CustomFunctionName("issuetype = XXX AND status = YYY")

This function does exactly what you need and returns the parents meeting the JQL criteria plus any subtask. Feel free to tweak it around.

import com.atlassian.jira.JiraDataType
import com.atlassian.jira.JiraDataTypes
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.MutableIssue
import com.atlassian.jira.issue.search.SearchProvider
import com.atlassian.jira.issue.search.SearchQuery
import com.atlassian.jira.jql.operand.QueryLiteral
import com.atlassian.jira.jql.parser.JqlQueryParser
import com.atlassian.jira.jql.query.IssueIdCollector
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.util.MessageSetImpl
import com.atlassian.query.clause.TerminalClause
import com.atlassian.query.operand.FunctionOperand
import ru.mail.jira.plugins.groovy.api.jql.ScriptedJqlValuesFunction

class DemoFunction implements ScriptedJqlValuesFunction {

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

@Override
public MessageSet validate(ApplicationUser searcher, FunctionOperand operand, TerminalClause terminalClause) {
def i18n = ComponentAccessor.getI18nHelperFactory().getInstance(searcher)
def numberValidMessage = new NumberOfArgumentsValidator(1i, i18n).validate(operand);
if (numberValidMessage.hasAnyErrors()) {
return numberValidMessage
}
def messageSet = new MessageSetImpl();
JqlQueryParser jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser)
try {
def query = jqlQueryParser.parseQuery(operand.getArgs().get(0))
} catch (any) {
messageSet.addErrorMessage("not valid jql:${operand.getArgs().get(0)}");
messageSet.addErrorMessage("${any}");
}
return messageSet
}

List<QueryLiteral> getValues(QueryCreationContext queryCreationContext, FunctionOperand functionOperand, TerminalClause terminalClause) {
final List<QueryLiteral> literals = new LinkedList<>();
String jql = functionOperand.getArgs().get(0)
getIssuesByJQL(jql, queryCreationContext.getApplicationUser()).each { issue ->
literals << new QueryLiteral(functionOperand, issue.key)
issue.getSubTaskObjects().each { subTask ->
literals << new QueryLiteral(functionOperand, subTask.key)
}
}

return literals
}

private Collection<MutableIssue> getIssuesByJQL(String jql, ApplicationUser user) {
def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser)
def searchProvider = ComponentAccessor.getComponent(SearchProvider)

def query = jqlQueryParser.parseQuery(jql)
SearchQuery searchQuery = SearchQuery.create(query, user)
IssueIdCollector collector = new IssueIdCollector()
searchProvider.search(searchQuery, collector)
return collector.getIssueIds().collect { getIssue(it as Long) }
}

private MutableIssue getIssue(Long id) {
ComponentAccessor.issueManager.getIssueObject(id)
}
}

[Daniel Ebers]

Hi @Iago Docando

thanks for the great contribution - it came like a great coincidence as I'm dealing with the same challenge today as Emanuel.
Looking for exchange and advise... would be happy if you could assist.

When I read the requirement of Emanuel

JQL that returns all parent issues of a given type in a given state and their respective children

I tried JQL of Script Runner

issueFunction in subtasksOf("issuetype = Bug AND status = Open")

This gave me back all Sub-Tasks which have a parent of Issue Type "bug" and are in status "open".

Now I am wondering if there is any advantage of using following JQL query

issuetype = Bug AND status = Open and issueFunction in hasSubtasks()

This gave me back all issues of Issue Type "bug" that are in status "open" listed along with their sub-tasks.

Maybe I am not getting it right. From my understanding the first one lists only sub-tasks (but you can navigate to the parent with just one click), the latter one lists the parents itself.

The MyGroovy part looks amazing btw - see it for the very first time.

Cheers,
Daniel

[Iago Docando]

Hi, Daniel.

I'm not sure what do you need help with... You present 2 searches that return a different set of results each, as they should. You understand what you're looking for and apparently you're getting those results.

If both searches were suposed to return the same result we could maybe try to test wich one is the better one in terms of efficiency or speed to return the results but since both searches work fine I couldn't point to any "advantage" one might have over the other. It all depends on what do you want to find.

If I've missed your point please let me know because right now I don't understand your need.

PS_ I'm not a MyGroovy expert but I've used it a lot in the past. It has a very active and helpful community, I believe this function was given to me through their telegram group if I remember correctly.

[Daniel Ebers]

It all depends on what do you want to find.

Yes, you're right after all. I tried to check if I got the idea of Emanuel right or if it is what he is searching for. Maybe he will say something about it.

Efficiency and speed is also a good point but that can be neglected (at least for my use case at the moment, the query will be one kind of a one-shot).
For my query it is resolved meanwhile, so thanks for getting back on this :)