How to retrieve list of jira issues for multiple pkey?

Hi All,

I have a query which pulls list of components associated with issue_key and subquery pulls list of jira items by components, however in this query I have been able to pull jira items by only pkey, is it possible to pull list of jira bugs for multuple pkey?

bug-123 in following query is issue_key, I need to enter multiple pkey under set statement, any help is appreciable. (you can ignore component part, its a custom field)

here is query :

set @pkey := 'bug-123';
select jiraissue.*, co.*
from jiraissue,project,issuetype,nodeassociation,component,
customfieldvalue cv
,customfieldoption co
where
component.cname = (SELECT component.cname
FROM nodeassociation, component, jiraissue
WHERE component.ID = nodeassociation.SINK_NODE_ID
AND jiraissue.id = nodeassociation.SOURCE_NODE_ID
AND nodeassociation.ASSOCIATION_TYPE = 'IssueComponent'
AND pkey = @pkey) and
project.pkey = (SELECT substring_index(jiraissue.pkey,'-',1) as project_name
FROM nodeassociation, component, jiraissue
WHERE component.ID = nodeassociation.SINK_NODE_ID
AND jiraissue.id = nodeassociation.SOURCE_NODE_ID
AND nodeassociation.ASSOCIATION_TYPE = 'IssueComponent'
AND pkey = @pkey) 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
and jiraissue.id = cv.issue
and cv.stringvalue = co.id
and cv.customfield = 10020;

3 answers

The SQL query you have is unnecessarily complicated. If you already have the pkey, that’s all you need. You don’t need to search for the component and then match on the component again, and then do the same thing with the product.

If your pkeys are “bug-123,” “issue-456,” “defect-789,” all you need is:

select jiraissue.* from jiraissue where jiraissue.pkey in ("bug-123", "issue-456", "defect-789");

Cedric,

Thank you for your response, I should have been more specific while posting the comment, query mentioned above pulls list of jira (issue = bug) by component, I have several components in a single project. I believe your query won't solve my issue.

Alok

You need to explain more. What do components have to do with this? The pkey is more specific than component. If you’re selecting the issues by pkey, then it’s redundant to select them by component as well.

Or are you using the term “pull items” to mean something different than searching?

Or do you need more fields returned for each issue in the results set?

Or is the result set something different than the issues specified by pkey?

Or are you trying to do something else?

Hi Cedric,

Being more specific with query this time, in following query , abc-123 is a bug id, the component associated with abc-123 is "star", there are 5 bugs in jira project which has component "star". In the following query, subquery (look after component.cname ) gives me a component "star", section after subquery gives me a list of jira items associated with component "star", this works well when I use "set @pkey = 'abc-123', now i need to add 'def-456' to following query, thus I have 'set' @pkey1 for new jira bug "def-456", in subquery I added @pkey1, however it throws an error message, I should be able to add multiple jira bugs which can give me a consolidated list of jira items using following query, following query works with single entry however throws an error message when I try to set more than 1 pkey, hope this helps.

set @pkey = 'abc-123';
set @pkey1 = 'def-456';
select jiraissue.*, co.*
from jiraissue,project,issuetype,nodeassociation,component,
customfieldvalue cv
,customfieldoption co
where
component.cname = (SELECT component.cname
FROM nodeassociation, component, jiraissue
WHERE component.ID = nodeassociation.SINK_NODE_ID
AND jiraissue.id = nodeassociation.SOURCE_NODE_ID
AND nodeassociation.ASSOCIATION_TYPE = 'IssueComponent'
AND pkey in (@pkey,@pkey1) 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
and jiraissue.id = cv.issue
and cv.stringvalue = co.id
and cv.customfield = 10020;

I made a mistake on my original answer. In the original question, there’s the line

set @pkey := 'bug-123';

so I thought the questioner was using “pkey” to refer to the unique issue identifier, or “issuekey.”

But, of course, “pkey” doesn’t mean that. It’s actually the alphabetic abbreviation unique to each product used in the issuekey. For the issuekeys “bug-123,” “issue-456,” “defect-789,” the corresponding pkeys, (aka product keys) are “bug,” “issue,” and “defect.”

Let’s use the sample pkeys as “NINA,” “PINTA,” and “MARIA,” just so there’s no confusion.

That means the SQL would be

select jiraissue.* from jiraissue where jiraissue.pkey in ("NINA", "PINTA", "MARIA");

You’re still not being clear. But I think I might be starting to understand you.

Let me see if I am actually understanding you.

Let’s start from the case where you just have one issue: “abc-123.”

abc-123 has one component, “star.”

What are you trying to find?

I think you want to find all the issues that have the same component as “abc-123.” That is, issues with component “star.”

What happens if “abc-123” has more than one issue? Say, “star” and “supernova.”

I think you want to find all issues with either component “star” or componet “supernova.”

Now let’s say you have 2 issues: “abc-123” and “def-456.”

def-456 has one component, “understudy.”

What do you want to find now?

I think you want to find all the issues that have a component that matches a component in either “abc-123” or “def-456.” In other words, “star,” “supernova,” or “understudy.”

Is that correct?

If that’s the case,

select
  jiraissue.*
from
  jiraissue,
  nodeassociation
where
  jiraissue.id = nodeassociation.source_node_id
  and nodeassociation.association_type = 'IssueComponent'
  and nodeassociation.sink_node_id in (
    select
      nodeassociation.sink_node_id
    from
      nodeassociation,
      jiraissue
    where
      nodeassociation.source_node_id= jiraissue.id
      and nodeassociation.association_type = 'IssueComponent'
      and jirassue.pkey in ("abc-123", "def-456")
  );

Basically, you use the subquery to select the component IDs associated with the issues given by the pkeys. You then select the issues associated with those component IDs.

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published yesterday in Jira

5 ways you can make the most of Jira Software and Bitbucket Cloud

As part of the Bitbucket product team I'm always interested in better understanding what kind of impact the use of our tools have on the way you work. In a recent study we conducted of software devel...

46 views 0 4
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