View issue details along with linked issue details

MuthuKumar May 26, 2016

I have an issue FR1 which has a custom field with values TCD1, TCD2 & TCD3.  

I have another issue DT1 which has a custom field with value TCD2.  

Now, I need a query or gadget to view following  

DT1, TCD2, FR1

8 answers

1 vote
Pablo Beltran
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 27, 2016

A similar use case (linking via custom field but the custom field contains an issue key rather than a generic value) is being resolved by a lot of users with SQL for JIRA. HOWEVER, your use case is MUCH MORE interesting and COMPLEX, and fortunately it is also resolvable with SQL for JIRA. BUT you have to have a MEDIUM-ADVANCED  SQL user SKILL because the SQL is NOT TRIVIAL wink at the time that is one of the most beatiful SQL queries you might want to create and a perfect use case to show the power of SQL for JIRA to the rest of the World:

These would be the major steps to build your SQL for JIRA query:

A) Given a issue with key 1 for the project PROJ_A  (PROJ_A-1)  you have to join the ISSUECUSTOMFIELD table to get the custom field (ie: named "CF_X")  

select 'CF_X = ' || CF_X  as "JQL", i1.key as "KEY", CF_X as "CF_X" from issues i1 inner join issuecustomfields cf1 on cf1.issueid = i1.id where i1.key='PROJ_A-1'

IMPORTANT: Note please that the query above returns a JIRA JQL as the first column by concatenating the 'CF_X = ' string to the CF_X value. I.e: If you perform the query in the built-in SQL console you would get a string similar to:

CF_X = XXX

guessing that the CF_X custom field value is XXX (of course, you would have to use double quotes for no one-word strings: CF_X = "XXX"

B) Now, you have to use the query above as INPUT table for a JOIN (this is the trick!). You could think on the query above as a sort of virtual table named "V" for instance with three columns (JQL, KEY, CF_X).


select v.KEY, v.CF_X, i2.key from (

select 'CF_X = ' || CF_X  as "JQL", i1.key as "KEY", CF_X as "CF_X" from issues i1 right join issuecustomfields cf1 on cf1.issueid = i1.id where i1.key='PROJ_A-1'

) v inner join issues i2 on i2.JQL = v.JQL

Please take a minute of your time to understand how the query above will be resolved by the H2 database engine: first, the "virtual" V table will be created on the fly by performing the embedded query (A) which will return a JIRA JQL. The JQL query will be used as input to populate the joined ISSUES i2 table, so you are getting all the issues from JIRA having the CF_X with XXX value via JQL. 

C) Next step is joining the ISSUECUSTOMFIELD table again (cf2) to get the custom fields for the issues (B) and filter by the CF_X condition:

select v.KEY, v.CF_X, i2.key from (

select 'CF_X = ' || CF_X  as "JQL", i1.key as "KEY", CF_X as "CF_X" from issues i1 inner join issuecustomfields cf1 on cf1.issueid = i1.id where i1.key='PROJ_A-1'

) v right join issues i2 on i2.JQL = v.JQL

inner join ISSUECUSTOMFIELDS cf2 on cf2.issueid = i2.id WHERE cf2.CF_X = v.CF_X

D) Finally, you might want to discard the "reflexive" solution of the PROJ_A-1 issue joined to itself. In other words, you don't want the

PROJ_A-1, XXX, PROJ_A-1 

result.

You can do it via SQL by adding a new condition to WHERE clause in the query above:

AND i2.key != v.KEY

Or more interestingly (and advanced, it will also perform better) by excluding it from the JQL in the (A) query:

select 'CF_X = ' || CF_X  || ' AND key != ' ||  i.key as "JQL", i1.key as "KEY", CF_X as "CF_X" from issues i1 inner join issuecustomfields cf1 on cf1.issueid = i1.id where i1.key='PROJ_A-1'


Voila! smile

Disclaimer: I've don't tested it, so it might have typo errors, etc.

And of course, there are also some alternatives to visualize those data (ie: via gadget, i.e: via custom field and much more are coming soon). 

Pablo Beltran
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 27, 2016

You could also transform the query above into a new JQL query by using the built-in sql function to get the "linked" issues via custom field value and use it as quick filter for your Agile backlog.

MuthuKumar May 27, 2016

Thanks Pablo. Hope this will help me to solve the issue.

I'll check these and update you. 

Steven F Behnke
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 28, 2016

Nice!

0 votes
MuthuKumar May 26, 2016

Yes, the link will appear on the issues linked from.

But as a report if I want to see all the issues in a project and the list of issues linked to that issue, then I have to fetch details from multiple issues (at least two different issues). Is it possible, something like relational database query?

Thanks. 

0 votes
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 26, 2016

Links will appear on the issues you've linked from, so that should solve the problem for you.

Matching fields in custom fields could be done, but it requires code, and if you have a lot of issues with a particular value, you're going to have performance issues.  

You shouldn't be trying to do vast numbers of links of either type in an issue really - that's what searching is for.

0 votes
MuthuKumar May 26, 2016

My main requirement is to view/fetch details from two different issues in JIRA using link or this kind of relationship (i.e. matching values in custom fields). If this can be done without link it will be great.

Thanks.

0 votes
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 26, 2016

So you're saying you want to link issues without linking them?  Using a custom field instead of a link?  Is that right?

0 votes
MuthuKumar May 26, 2016

Hi Steven,

I have two issues in JIRA and there are values in the customer fields to relate both the issues. Now I need to fetch details from both the issues using this relationship. There is no links created between these two issues in JIRA. If Link is required to fetch details from both the issues then I may create it. 

Issue #1 :

Key = FR1

Custom Field x : TCD1, TCD2, TCD3

 Issue #2:

Key = DT1

Customer Field y : TCD2

 I want to view below:

 Issue#2.Key, Issue#2.Custom Field y, Issue#1.Key

 Condition may be Issue#1.Customer Field x contains Issue#2.Customer Field y

Let know if I'm still not clear. Sorry, I'm not able to include image.

Thanks. 

0 votes
GabrielleJ
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 26, 2016

My mind curled up a bit there

0 votes
Steven F Behnke
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 26, 2016

Can you draw a simple diagram? I'm not clear...

Suggest an answer

Log in or Sign up to answer