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
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 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!
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).
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks Pablo. Hope this will help me to solve the issue.
I'll check these and update you.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Nice!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
So you're saying you want to link issues without linking them? Using a custom field instead of a link? Is that right?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
My mind curled up a bit there
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Can you draw a simple diagram? I'm not clear...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.