How to retrive list of bugs associated with a component in JIRA project (SQL Query)

[Alok Band]

Hi

I am trying to retrieve list of bugs associated with a component in JIRA project, can someone help me with query. I have been able to extract component from JIRA project, just need some help with list of bugs associated with component.

Thanks

[Dieter]

Here is the solution to search for isssue with Component 'My component' in project with key 'JRA'

select jiraissue.* from jiraissue,project,issuetype,nodeassociation,component 
where 
component.cname = 'My component' and 
project.pkey = 'JRA' and 
issuetype.pname = 'Bug' and
jiraissue.project = project.id and 
jiraissue.issuetype = issuetype.id and 
nodeassociation.association_type = 'IssueComponent' and
nodeassociation.source_node_entity = 'Issue'  and
nodeassociation.source_node_id = jiraissue.id  and
nodeassociation.sink_node_entity = 'Component'  and
nodeassociation.sink_node_id = component.id

[Alok Band]

Dieter, Thanks for the response , appears query is returning 0 rows. I believe component is a unique value in entire JIRA project why do we need to associate project(.pkey) with it?

[JamieA]

The ID is unique but not the name. If you want to query by component ID alone you wouldn't need to join on project.

[Dieter]

If you have the component ID you could use this

select jiraissue.* from jiraissue,issuetype,nodeassociation,component 
where 
component.id = 12345 and 
issuetype.pname = 'Bug' and
jiraissue.issuetype = issuetype.id and 
nodeassociation.association_type = 'IssueComponent' and
nodeassociation.source_node_entity = 'Issue'  and
nodeassociation.source_node_id = jiraissue.id  and
nodeassociation.sink_node_entity = 'Component'  and
nodeassociation.sink_node_id = component.id

[Dieter]

Or completely with id's

select jiraissue.* from jiraissue,nodeassociation
where 
jiraissue.issuetype = 3 and 
nodeassociation.association_type = 'IssueComponent' and
nodeassociation.source_node_entity = 'Issue'  and
nodeassociation.source_node_id = jiraissue.id  and
nodeassociation.sink_node_entity = 'Component'  and
nodeassociation.sink_node_id = 12345

just this looks less readable at first ...

[Alok Band]

Thank you it works :)

[Dieter]

If the project key is 'JRA' , the name of the component is 'My Component and the name of the issue type is 'Bug' you could use this:

select jiraissue.* from jiraissue,project,issuetype,nodeassociation,component 
where 
component.cname = 'My Component' and 
project.pkey = 'JRA' and 
issuetype.pname = 'Bug' and
jiraissue.project = project.id and 
jiraissue.issuetype = issuetype.id and 
nodeassociation.association_type = 'IssueComponent' and
nodeassociation.source_node_entity = 'Issue'  and
nodeassociation.source_node_id = jiraissue.id  and
nodeassociation.sink_node_entity = 'Component'  and
nodeassociation.sink_node_id = component.id;

[Adam Saint-Prix]

Hi Alok,

Try using the following query in the advanced search (JQL)

project = ProjectName and issuetype = Bug and component = "Your Component"

If you don't care what the project is or if it's cross project, you can just do

issuetype = Bug and component = "Your Component"

Replace "Your Component" with the component name you'd like to search for...

[Alok Band]

Hi Adam,

Thank you for response, I will give it a try. However, I am looking for SQL query, I hope someone will post the query :)

[Adam Saint-Prix]

Ah! Sorry. Misread the title of the post.