How to retrieve list of jira issues for multiple pkey?

[Alok Band]

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;

[CEDRIC ZABEL]

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.

[CEDRIC ZABEL]

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

[CEDRIC ZABEL]

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

[Alok Band]

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

[CEDRIC ZABEL]

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?

[Alok Band]

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;