ISSUELINKS | |||||
---|---|---|---|---|---|
Column | ISSUEID | DIRECTION | TYPE | LABEL | CHILDISSUEID |
Type | BIGINR | VARCHAR | VARCHAR | VARCHAR | BIGINT |
Indexed | x |
Example:
Get all the not resolved linked issues that are being blocked
This is a very interesting use case, as you can write your query without using this table in a lot of cases. Pay attention.
DEMO-1 blocks → DEMO-2
From the DEMO-1 perspective, it has a link of type "Blocks" pointing to DEMO-2 with label "blocks" and outward direction.
From the DEMO-2 perspective, it has a ink of type "Blocks" pointing to it from the DEMO-1 with label "is blocked by" and inward direction.
DEMO-1 is blocked by ← DEMO-1
When two issues are linked, Jira creates two links as described above:
SELECT KEY(JQl.ISSUE) as "Source", CASEWHEN(IL.DIRECTION = 'outward', ILT.OUTWARDDESCRIPTION, ILT.INWARDDESCRIPTION) AS "Link", KEY(JQL2.ISSUE) as "Target" FROM JQL JOIN ISSUELINKS IL ON IL.ISSUEID = JQL.ISSUEID JOIN ISSUELINKDEFINITIONS ILT ON ILT.ID = IL.ISSUELINKTYPEID JOIN JQL JQL2 ON JQL2.ISSUEID = IL.CHILDISSUEID WHERE JQL.QUERY = ' issue = DEMO-1 ' AND RESOLVED(JQL2.ISSUE) IS NOT NULL |
Source | Link | Target |
DEMO-1 | blocks | DEMO-2 |
The query above
However, this does not look very optimized as there are a lot of joined tables and a lot of calls to the underlying Jira API.
This can be re-written in a compacter way by using JQL to search for the not resolved blocked issues.
First, use SQL to write the JQL query that will be performed later.
SELECT KEY(JQL.ISSUE) as "Source", 'issue in linkedissues("' || JQL.ISSUEID || '", "blocks") AND RESOLUTION IS EMPTY' as "Target JQL" FROM JQL WHERE JQL.QUERY = ' issue = DEMO-1 ' |
Source | Target JQL |
10000 | issue in linkedissues("10000") AND RESOLUTION IS EMPTY |
Please pay attention to the single and double quotes enclosing stings:
Now, you got a JQL that has to be performed for each issue. So, use the JQL table to run them:
SELECT "Source" , 'blocks' as "Link" , KEY(JQL.ISSUE) as "Target" FROM ( SELECT KEY(JQL.ISSUE) as "Source", 'issue in linkedissues("' || JQL.ISSUEID || '", "blocks") AND RESOLUTION IS EMPTY' as "Target JQL" FROM JQL WHERE JQL.QUERY = ' issue = DEMO-1 ' ) X LEFT JOIN JQL ON JQL.QUERY = X."Target JQL" |
DEMO-1 | blocks | DEMO-2 |
You got just the same results. This is a good example of the flexibility of SQL+JQL.
In the query above note:
How does the query above works?
Interesting, no?