Script Runner "single issue picker" database schema

[Andrew Warburton]

I have added a custom field of type "Single Issue Picker" and would like to query the value for this in SQL.   Which table stores the issue selected for this custom field?

[Christos Moysiadis]

Hello @Andrew Warburton 

Here i have something for you to try:

select *
from customfield cf , customfieldvalue cfv, jiraissue iss
where cf.cfname='SinIssPick' and cfv.customfield = cf.id and iss.id = cfv.issue

Regards

[Contemi-EUR: Andrew Warburton]

Hi @Christos Moysiadis 

Thanks, that helps but I need to add a where clause to restrict the results by the "Single Issue Picker" field value.   There is a "stringvalue" column on customfieldvalue but it has a number.  Do you know where I get the value for the single issue picker? 

Thanks

[Pierre Ibrahim]

@Contemi-EUR: Andrew Warburton based on this answer https://community.atlassian.com/t5/Jira-Service-Management/Jira-Database-Table-for-Scriptrunner-Custom-Field/qaq-p/1462817 it doesn't seem possible to return the value of scripted fields.

Usually you'd join customfieldoption on customfieldvalue.stringvalue = customfieldoption.id to get the actual value in cases where customFieldValue has a number in the stringvalue.

However for scriptrunner fields, customfieldoption seems to be blank. I'm still researching this and will update if I find a way to get that result.

Edit: The number value is the issue ID.

select concat(p2.pkey, '-', ji2.issuenum), concat(p.pkey, '-', ji.issuenum) from jiraissue ji
inner join project p
on p.id = ji.project
inner join customfieldvalue cv
on cv.ISSUE = ji.id
and cv.CUSTOMFIELD = 'YourCustomFieldIdHere'
inner join jiraissue ji2
on ji2.id = cv.STRINGVALUE
inner join project p2
on p2.id = ji2.project

The above MySQL query (you'll need to modify if you're on a different database engine) will give you in the first column the issue key from the single issue picker, and the second column is the main issue key