Query to retrieve the JIRA tasks associated with a project, along with the component they are tied to

Hi,

Currently, i am using below query to retrieve the list of all JIRA tasks associated with a project (Project key = Test) based on the project key. Below query returns the expected data. However I am looking to modify the query so as to retrieve 'Component' information associated with the JIRA tasks.

SELECT JI.pkey AS Issue_ID, CG.CREATED AS Modified, CG.AUTHOR AS Modified_By, CI.FIELD AS Field_Modified, CI.NEWSTRING AS New_Value FROM jiraissue JI, changegroup CG, changeitem CI WHERE JI.ID=CG.ISSUEID AND CG.ID=CI.GROUPID AND JI.PKEY like '%Test-%' AND CI.FIELD = 'Stage' ORDER BY JI.ID;

Could you please let me know what changes are to be made to above query so as to retrieve the component information of all JIRA tasks, for a particular project?

Thanks,

Shankar

1 answer

This widget could not be displayed.

You need to read nodeassociation (for the links to components) and the components table (for the data about the component)

Include something like this in your query (which is a copy and paste from the docs - https://confluence.atlassian.com/display/JIRA041/Database+Schema#DatabaseSchema-Componentsandversions )

select * from component where id in (
    select SINK_NODE_ID from nodeassociation 
           where ASSOCIATION_TYPE='IssueComponent' 
           and SOURCE_NODE_ID=(
               select id from jiraissue where pkey='JRA-5351')
    );

(Sorry, I'm not a DBA and don't really think in SQL, so I didn't try to unpick your SQL and add that in as I'd probably get it wrong - I think the general concept is more useful to you, and you can make a neater and better job of using it than I would!)

Nic,

Thanks very much for the response.

I have modified the query so as to retrieve the component information for all JIRA tickets in a particular project. However i am unable to retrieve the tickets which do not have component information associated with them i.e. the tickets which have the component value as 'None' against them.

Could you please let me know how can i retrieve all tickets i.e. which have component information associated with them and the ones which have the component value as None?

Below is the revised query i have used.

SELECT JI.pkey, CG.CREATED, CG.AUTHOR, CI.FIELD, CI.NEWSTRING, IT.pname, PR.pname, COMP.cname

FROM jiraissue JI, changegroup CG, changeitem CI, issuetype IT, priority PR, component COMP,

nodeassociation NODASSOC

WHERE JI.ID = CG.ISSUEID

AND CG.ID = CI.GROUPID

AND JI.ISSUETYPE = IT.ID

AND JI.PRIORITY = PR.ID

AND comp.ID = nodassoc.SINK_NODE_ID

AND nodassoc.SOURCE_NODE_ID = JI.ID

AND nodassoc.ASSOCIATION_TYPE = 'IssueComponent'

AND JI.PKEY like '%TST-%'

AND CI.FIELD = 'TST Stage'

ORDER BY JI.ID, JI.pkey;

Thanks,

Shankar

Ah, no, the point of "none" is to explain to the user that there are no components associated at all. In the database, there's no data at all for "none". From a database/Jira point of view, the logic is: if no nodeassociations for components are find, display "none"

I'm sure there's a way to pull that out in SQL, but I'm out of my depth there. My memory is squeaking something about explicitly using "Left Join" when joining issues to nodeassociation, but I'm really not sure

Nic,

If you could provide me your inputs on how the query should look like, i would be able to move forward. Please do let me know your thoughts.

Thanks,

Shankar

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted Aug 06, 2018 in Jira Service Desk

A is for Activate: Share your top Jira Service Desk onboarding tips for new users!

Hi, everyone! Molly here from the Jira Service Desk Product Marketing Team :).  In the spirit of this month's  august-challenge, we're sourcing stories of Jira Service Desk activation fro...

578 views 25 15
Join discussion

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