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

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)?

3 answers

1 accepted

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.

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!

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? 

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'?  

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? 

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

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

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





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

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Bridget Sauer
Published 11 hours ago in Marketplace Apps

Calling all developers––You're invited to Atlas Camp 2018

 Atlas Camp   is our developer event which will take place in Barcelona, Spain  from the 6th -7th of   September . This is a great opportunity to meet other developers and get n...

35 views 0 3
Read article

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you