MySQL query to list of all issues by issue type including customfields value

[rozuan]

Hi All,

I need help to list all of the issues details on jira by issue type in mysql query including customfields.

Anyone have experienced this? Please help me by giving some example.

I would like to display customfield option value. For example I have a few custom field such as Type, Contract, Source and Category. How to display these all including basic from jiraissue table?

I have my own dummy sql statement but I dont know how to include the customfields.

SELECT jiraissue.id,jiraissue.pkey,project.pname AS project,jiraissue.summary,issuetype.pname AS issuetype,
issuestatus.pname AS status,jiraissue.assignee,jiraissue.reporter,jiraissue.created,
jiraissue.resolutiondate,jiraissue.duedate,jiraissue.description,customfieldvalue.customfield,
customfieldoption.customvalue, customfield.cfname

FROM jiraissue,project,issuetype,resolution,issuestatus,customfieldvalue,customfield,customfieldoption

WHERE jiraissue.project=project.ID AND jiraissue.issuetype=issuetype.ID 
AND jiraissue.resolution=resolution.ID AND jiraissue.issuestatus=issuestatus.ID 
AND jiraissue.issuetype IN (43,40,37,39)

Thank you.

[Udo Brand]

To see the values of the customfields in your issues you need to take a look at customfieldvalue.stringvalue, customfieldvalue.numbervalue, etc. depending on the field type.

No need to join customfieldoption in your query (this table contains all options of a customfield like select list, multi check box, etc.)

[rozuan]

I want the result should be like this. Maybe you can help me with the query?

thank you

[Udo Brand]

This is not that simple since you would need to join customfieldvalue for each customfield you want to display. I don't know your fields and frankly I currently don't have that time.

Why don't you use JQL and export that data?

By the way, you need to outerjoin resolution because there could be unresolved issues.

[rozuan]

If I could, I would use the JQL. However, as you can see in the attachment, the duedate field didnt support datetime format. thats why I need to export using sql query. Apreciate if you could help me to do some example on four of my customfields only to display the value of each customfield. I have done a few query and cannot get the right result which i dont know where in the database (which field and table) that store the value of selected customfield. For example (refer my attachment above), I have four custom fields and each of them had multiple option for client to select during creating the issue. When they select the value for each of the customfield, where is it store in database? I found only list of all value for each customfield only and not the value that client select. Any idea?

[Udo Brand]

something like this should work

SELECT jiraissue.id,
jiraissue.pkey,
project.pname AS project,
jiraissue.summary,
issuetype.pname AS issuetype,
issuestatus.pname AS status,
resolution.pname as resolution,
jiraissue.assignee,
jiraissue.reporter,
jiraissue.created,
jiraissue.resolutiondate,
jiraissue.duedate,
jiraissue.description,
(select cfv1.textvalue from customfieldvalue cfv1 where cfv1.issue = jiraissue.id and cfv1.customfield = 10000)as customfield1, --textfield
--repeat this for each customfield; watch the field type and select the correct column in custumfieldvalue
(select cfv2.numbervalue from customfieldvalue cfv2 where cfv2.issue = jiraissue.id and cfv2.customfield = 10101)as customfield2 --numberfield
FROM jiraissue join project on jiraissue.project=project.ID 
join issuetype on jiraissue.issuetype=issuetype.ID
join issuestatus on jiraissue.issuestatus=issuestatus.ID
left outer join resolution on jiraissue.resolution=resolution.ID

[rozuan]

Thank you Udo,

Other fields are good but the customfields are null. I have select the correct field but its come with the error "Subquery returns more than 1 row". It seems that for each custom field stored multiple customvalue. Still dont know which field store the correct value when creating issue.

[Udo Brand]

Are those fields multi select, multi checkboxes or radio buttons?

[rozuan]

All of them is 'Select List'

[Udo Brand]

I use oracle there the subqueries need to look like this

(select listagg(cfv2.stringvalue, ', ') within group (order by 1)from customfieldvalue cfv2 where cfv2.issue = jiraissue.id and cfv2.customfield = 10302 group by issue ) as customfield2

in mysql I believe it needs to look like this

(select GROUP_CONCAT(cfv2.stringvalue order by cfv2.stringvalue DESC SEPARATOR ', ') from customfieldvalue cfv2 where cfv2.issue = jiraissue.id and cfv2.customfield = 10302 group by issue ) as customfield2

[rozuan]

Hi Udo, Im a bit confused here. Did your latest query will display all of the value from the select list custom field?

I need to display the selected list of the customfield only. And now I dont know which table and which field should I pull from.

Our customfields is store in customfield table and the select list values are store in customfieldoption and the custimfieldvalue table hold the connection between issue and customfield.

However which table are hold the value for the selected list when we create the ticket? the numbervalue/textvalue field in customfieldvalue table are all null.

Thank you for your help Udo. Appreciate a lot.

[Udo Brand]

> Did your latest query will display all of the value from the select list custom field?

No, only the selected ones

> And now I dont know which table and which field should I pull from.

The table is customfieldvalue and for select list the field is stringvalue

> However which table are hold the value for the selected list when we create the ticket? the numbervalue/textvalue field in customfieldvalue table are all null

Again, it depends on the custom field type. Number field store the value in numbervalue, date picker in datevalue, text fields in textvalue and e.g. select list in stringvalue.