Issue Links

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

The query above

1. Performs the JQL.QUERY = ' issue = DEMO -1'
2. Gets all the linked issues (DEMO-2) via the ISSUELINKS table
3. Needs to join to the ISSUELINKSDEFINITONS table to get the labels of the link for the inward and outward directions 
4. Needs to get the DEMO-2 issue by joining the JQL table again (aliased with the JQL2 name) to get the issue Java object and extract its key and resolved date

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 '

Please pay attention to the single and double quotes enclosing stings:

• Single quotes are for SQL strings
• Double quotes are for JQL strings

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"

You got just the same results. This is a good example of the flexibility of SQL+JQL.

In the query above note:

• A SELECT is used as part of a join, just like it was a table. Well, SELECT produces tables in SQL. And this table has the "X" alias.
• LEFT JOIN is used to ensure that the "X" table is performed first
• The "X" table has two columns: "Source" and "Target JQL" than can be referenced outside the encloser brackets.

How does the query above works?

1. X is performed and the issue key and a JQL query string is build for each issue.
2. The JQL built in (1) is performed via join JQL to get the unresolved blocked issues.

Interesting, no?