Compare Parent and Sub Task field values in JQL using ScriptRunner

[markk1]

Hi guys, 
I have a common field between parent and sub tasks called Risk Rating, and I was wondering if it was possible to compare these fields with Scriptrunner. I want to find all issues where the parent tickets sub task has a Risk Rating value higher than the Parent issues Risk Rating.  I've looked into using expression but I'm not sure this can be used with issueFunction sub task functionality, such as subTasksOf. Below is pseudocode for what I'm trying to achieve:

issueFunction in expression("", "subtask.RiskRating > parent.RiskRating")

Any help would be great.

Thanks!

[Andy Heinzer]

Hi Mark,

I don't think Jira and scriptrunner are going to be able to return to you in JQL exactly what you want here.  This is largely because JQL is designed to return Jira issues, it can't do these kinds of lookups dynamically on a parent issue and all subtasks of that parent and compare these values between these issues to then find the issue results.  It feels like this might be something that could be done via some nifty SQL against the Jira database, but that's not quite the same as the JQL in Jira.

The best I can figure is that you could use scriptrunner's JQL functions to do some basic lookups like this

issueFunction in subtasksOf("project = JRA and 'risk rating'=Low") AND 'risk rating' in (Medium, High, Highest)

This would return only subtasks that have a risk rating of medium, high, highest ONLY when the parent issue also has a risk rating value of Low.  This isn't an ideal solution, because the more values possible in that risk rating field means more and more JQL queries to try to capture all the possible scenarios.

We might have more luck trying to find these via SQL.   Could you let me know what kind of custom field type this risk rating is? Text field, number field, single select, etc?  It might help to know this if we want to try to look into a SQL query to find this information