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

[Shankar Rao Pala]

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

[Nic Brough -Adaptavist-]

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')
    );

[Nic Brough -Adaptavist-]

(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!)

[Shankar Rao Pala]

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

[Nic Brough -Adaptavist-]

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

[Shankar Rao Pala]

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