Issue Links

Example:

Get all the not resolved linked issues that are being blocked

This is a very interesting use case, as it many cases you can write your query without using this table. 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
  JQL.ISSUEID as "Issue",
  'issue in linkedissues("' || JQL.ISSUEID || '") AND RESOLUTION IS EMPTY' as "Target JQL"
FROM 
  JQL 
WHERE  JQL.QUERY = ' issue = DEMO-1 '