How to retrieve list of jira issues for multiple pkey?

Question

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 · Answer

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 · Answer

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

Alok Band · Answer

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;