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?
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
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!)
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,
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;
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
Atlassian Summit is an excellent opportunity for in-person support, training, and networking.Learn more
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!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG