Can you 'Order By' based on subquery results (issuefunction) in a search?

[Anthony Cardone]

Hello, 

I'm trying to create a query that shows what tickets in Project A are blocking Project B, and have them order by the rank field of Project B. This way I can have a priority order of what we need A to do to clear the path for A.

Here's a sample of my query. 

issueFunction in linkedIssuesOf("status not in (Closed) and project = B", "is blocked by") AND status = Open AND project = A

Can I order by Rank from within the subquery (or some other way)?

[Jonny Carter · Accepted solution]

One way to accomplish this would be to create a Scripted Field in that gives the rank of linked issues in Project B. You'd probably need some rule like "highest rank wins" to cover cases where an issue has more than one linked issue.

Then you could simply do "ORDER BY RankOfLinkedIssues", or whatever you decide to call your script field.

Be advised, there's some cacheing issues that need to be resolved when building script fields that calculate their values based off of linked issues. See https://scriptrunner.adaptavist.com/latest/jira/scripted-fields.html#_more_advanced_notes_on_caching and the listener created in a related example for more info: https://scriptrunner.adaptavist.com/latest/jira/recipes/scriptfields/workRemainingInLinkedIssues.html.

I don't believe that you can order by a subquery at all in JIRA, but a Script Field might let you emulate that functionality.

[Dee Elato]

I'm trying to do something similar.. I'm using Priority and Severity (custom field) to find out all the tickets that are not part of highest or lowest priority/severity and eventually OrderBy them based on the Priority and Severity. 

Here's my query: 

project in (Test A, Test B) AND issuetype in (Story, "Customer Reported Bug", Bug) AND status in (Waiting, Open) AND (priority not in ("0 - blocker", "1 - critical") AND Severity not in (Blocker, Critical)) AND (priority not in ("4 - trivial", "3 - minor") AND Severity not in (Minor, Trivial)) ORDER BY Priority DESC, Severity DESC

Eventually it doesn't return the correct results.. as in I need all sorts of permutation/combination of tickets that are not 0,1 in Priority AND Blocker, Critical in Severity.. Also, 3,4 in Priority AND Minor, Trivial Severity.  Meaning, a ticket with 1 priority but Minor Severity should show up in my search results. etc. etc.

Any thoughts? 

[Anthony Cardone]

Hi Dee, 

I'm going to assume the middle priority/severity is called 'Major' for the sake of my response...

From reading your query, I would think you'd only get back issues with 'Major' priority and 'Major' severity, so they would all be ranked the same. 

Is that what you're trying to do, or are you trying to find issues where either Priority OR severity (or both) has a value of 'Major'?  

[Dee Elato]

Hi Anthony, 

You got it almost correct. I have 5 levels of Priority and Severity i.e 

Priority – "0 - blocker", "1 - critical", "2 - major", "3 - minor", "4 - trivial"

Severity - Blocker, Critical, Major, Minor, Trivial

Next I created 3 filters to handle all the tickets that may have various combination of Priority and Severity. 

1st Filter gives me all of the HIGH priority and severity tickets – Give me all tickets with Priority in (0,1) AND Severity (Blocker, Critical) only.

2nd Filter gives me all of the LOW priority and severity tickets – Give me all tickets with Priority in (3,4) and Severity (Minor, Trivial) only.

These 2 filters are good and works fine. Now, I need a third filter that will include all of the bulk tickets that are Major, Major and also matches the other combinations, such as:

Priority = Blocker AND Severity = Minor,

Priority = Critical AND Severity = Minor,

Priority = Blocker AND Severity = Major,

Priority = Blocker AND Severity = Trivial,

Priority = Major AND Severity = Minor,

Priority = Trivial AND Severity = Major, etc. etc.

Does it make sense? 

[Anthony Cardone]

Yep, I think so. Could something like this work? (Breaking it out so it's easier to read)

(
project in (Test A, Test B) AND
issuetype in (Story, "Customer Reported Bug", Bug) AND
status in (Waiting, Open)
) 

AND

(
(priority not in ("0 - blocker", "1 - critical") AND Severity in (Blocker, Critical, Major)
OR
(priority in ("0 - blocker", "1 - critical", "2 - major") AND Severity not in (Blocker, Critical)
)

ORDER BY Priority DESC, Severity DESC

[Dee Elato]

Umm.. I made a small tweak... this query is not correct syntax-wise but I hope it gives you the idea on what I am trying to achieve here. Btw not sure how to correct and execute this in JIRA.

(
project in (Test A, Test B) AND 
issuetype in (Story, "Customer Reported Bug", Bug) AND 
status in (Waiting, Open)
) 

AND 

tickets not in (

(priority in ("0 - blocker", "1 - critical") AND Severity in (Blocker, Critical))
OR
(priority in ("3 - minor", "4 - trivial") AND Severity in (Minor, Trivial))

)

ORDER BY Priority DESC, Severity DESC

[Anthony Cardone]

I think we're saying the same thing in different ways... When you run this version, how are the results?

(project in ("Test A", "Test B") AND issuetype in (Story, "Customer Reported Bug", Bug) AND status in (Waiting, Open)) AND ((priority not in ("0 - blocker", "1 - critical") AND Severity in (Blocker, Critical, Major) OR (priority in ("0 - blocker", "1 - critical", "2 - major") AND Severity not in (Blocker, Critical)))) ORDER BY Priority DESC, Severity DESC

[Dee Elato]

Results are correct. I see what you're saying. You're correct. Thanks for analyzing and helping me out with this. Highly appreciate it. 

[Anthony Cardone]

Thank you Jonny!

I'm going to chat w/ the team that asked me for it and see if this is a short-term plan I'd work around it (with an automated export with logic built into a spreadsheet) or something they need long term (I'll go the Scripted Field route you recommended and look into caching issues).

Have a great day!