Issue Custom Fields

The two tables above replace the older ISSUECUSTOMFIELDS table. The newer tables bring a lot of benefits:

• Better performance
• Dynamic (it is no longer required re-start the plugin if a new custom field is added, modified or removed by JIRA or a third party plugin)
• Custom fields' original names preserved

Since the 6.1.0 version a new Custom Fields - related table is supported: ISSUECUSOMTFIELDOPTIONS

It allows to read all the possible values for custom fields based on lists.

Example 1

Getting all the custom fields

select 
     KEY(JQL.ISSUE) as "Issue", 
     cfd.NAME as "Custom field", 
     cfv.VALUE as "Custom field value" 
from 
     JQL
         inner join
     ISSUECUSTOMFIELDVALUES cfv on cfv.ISSUEID = JQL.ISSUEID
         inner join
     ISSUECUSTOMFIELDDEFINITIONS cfd on cfd.ID = cfv.CUSTOMFIELDID 
where 
     JQL='reporter=currentUser()'

Example 2

Old style

The query above returns one record per custom field. You might want to build a report with one only record (line) per issue and each custom field as column (this is the  way that the deprecated ISSUECUSTOMFIELDS table worked). You might want to report issues in JIRA Agile/Software displaying two custom fields as columns: Sprint (10005) and Story Point(10100), then the ISSUUECUSTOMFIELDVALUES has to be joined one per custom field:

select
       key(JQL.issue) as "Issue",
       cfv1.value as "Sprint",
       cfv2.value as "Story Point" 
from 
       JQL
           join 
       issuecustomfieldvalues cfv1 on cfv1.customfieldid = 10004 and cfv1.issueid = JQL.issueid 
           join 
       issuecustomfieldvalues cfv2 on cfv2.customfieldid = 10006 and cfv2.issueid = JQL.issueid
where 
      JQL.query ='reporter = currentUser()'

Note please, that the query uses LEFT (OUTER) JOIN to include issues with null values for the Sprint or the Story Point and the conditions on the customfieldid indexed column are set on the JOIN condition instead of the WHERE clause.

New style

The query above can be optimized a lot with the CF() function to extract data from the JQL.issue object:

select
       key(JQL.issue) as "Issue",
       cf(JQL.ISSUE, 10004) as "Sprint",
       cf(JQL.ISSUE, 10006) as "Story Point" 
from 
       JQL
where 
      JQL.query ='reporter = currentUser()'

Writing queries independent of the Jira instance

Many organizations have different environments for their life cycle (development, staging, production, etc). And the custom field ids can be different in each instance. However, it is possible to re-write the query above to be use the custom field names instead of their ids. See how:

First, build a view with the ids of each targeted custom field:

select 
    cfd1.id as "Sprint Id", 
    cfd2.id as "Story Points Id"
from 
    issuecustomfielddefinitions cfd1 
       join
    issuecustomfielddefinitions cfd2 
where 
    cfd1.name = 'Sprint' 
       and
    cfd2.name = 'Story Points'

In the query above the same ISSUECUSTOMFIELDDEFINITONS table is joined twice without any constraint (JOIN... ON). The database engine will perform all the MxN combinations, but in this particular case this is not a problem as it is a 1x1 combination which will perform good.

Now, build a new view as combination of the table above with all the records of your JQL:

select 
        * 
from
	(select 
		cfd1.id as "Sprint Id", 
		cfd2.id as "Story Points Id"
	from 
		issuecustomfielddefinitions cfd1 
		   join
		issuecustomfielddefinitions cfd2 
	where 
		cfd1.name = 'Sprint' 
		   and
		cfd2.name = 'Story Points')
	join
	(select *
	   
	from
		JQL
	where
		JQL.query = 'project SSP'
	)

It's almost done. In the query above there are not join constraints either. This will build all the combinations (MxN) again. However, M is 1 and N are all the records (issues) of the JQL. Therefore, there are performance drawbacks and it will perform good. 

Simply, extract the custom fields value from the issue obects (third column) by using the CF() function:

select 
        key(Issue) as "Issue",
        cf(Issue, "Sprint Id") as "Sprint",
        cf(Issue, "Story Points Id") as "Story Points"
from
	(select 
		cfd1.id as "Sprint Id", 
		cfd2.id as "Story Points Id"
	from 
		issuecustomfielddefinitions cfd1 
		   join
		issuecustomfielddefinitions cfd2 
	where 
		cfd1.name = 'Sprint' 
		   and
		cfd2.name = 'Story Points')
	join
	(select
                issue
	from
		JQL
	where
		JQL.query = 'project = SSP'
	)

No custom field id is hard coded anywhere.