Getting the issue type scheme of all projects in database.

[David Garcia]

Hi!

It's very important for me get the issue type scheme associated of all projects in database. I dont find the way to get it. I don't know wich query I should execute.

Plese, help me.

Thanks so much!

[Marcus Silveira]

Hi David,

This should return all Issue Types associated to the project "Test".

select IT.pname from project P
join configurationcontext CC on P.id = CC. project
join fieldconfigschemeissuetype FCI on FCI.fieldconfigscheme = CC.fieldconfigscheme
join optionconfiguration OC on OC.fieldconfig = FCI.fieldconfiguration
join issuetype IT on IT.id = OC.optionid
where P.pname = 'Test'

Hope this helps

[Sivarama Krishna K]

Able to get the results as expected.

Thank you.

[David Garcia]

Thanks Marcus, but i need all the projects that doesnt have the default issue type scheme.

I think the correct query is:

select project.pname, pkey
from project
where project.ID not in (
select project.ID
from project
join configurationcontext
on project.ID = configurationcontext.PROJECT
where configurationcontext.customfield = "issuetype"
and configurationcontext.FIELDCONFIGSCHEME != 10000
and configurationcontext.FIELDCONFIGSCHEME is not null)

Thanks Marcus!

[Marcus Silveira]

Hi David,

I thought you needed all issues types associated to a specific project.

My bad :)

Your query does seem to get the expected results.

If you need any help, just let me know.

[Naman Mandli]

Can you please help to get a list of projects which has issuetype 'Bug'?

[Radek Janata]

Hi,

I was dealing this too and here is my query (MySQL).

This returns list of all projects (key, name), associated issue type scheme and list of all issue types.

Adjust this query accordingly based on your needs.

SELECT
  p.pkey AS 'Project Key',
  p.pname AS 'Project Name',
  its.configname AS 'Issue Type Scheme',
  its.issuetypes AS 'Issue Types'
FROM project p
LEFT JOIN configurationcontext cc ON p.ID = cc.PROJECT
LEFT JOIN (
  SELECT
    fcsit.FIELDCONFIGSCHEME,
    fcsit.FIELDCONFIGURATION, 
    fcs.configname,
    GROUP_CONCAT(it.pname ORDER BY oc.SEQUENCE SEPARATOR ', ') AS issuetypes
  FROM optionconfiguration oc
  LEFT JOIN issuetype it ON it.ID = oc.OPTIONID
  LEFT JOIN fieldconfigschemeissuetype fcsit ON fcsit.FIELDCONFIGURATION = oc.FIELDCONFIG
  LEFT JOIN fieldconfigscheme fcs ON fcs.ID = fcsit.FIELDCONFIGSCHEME
  GROUP BY oc.FIELDCONFIG) its ON its.FIELDCONFIGSCHEME = cc.FIELDCONFIGSCHEME
WHERE cc.customfield = 'issuetype'
-- HAVING its.issuetypes RLIKE 'Support Ticket'
;

Using the last "HAVING" statement and RLIKE or NOT RLIKE you can filter for various  issue types.

[Arun Sharma]

Works Great!

Thank you

[Grzegorz Ziolek]

@Radek Janata Hi

I get below error, any idea how can this be addressed?

[42000][1055] Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'jiradb.fcsit.FIELDCONFIGSCHEME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

[Radek Janata]

@Grzegorz Ziolek

What error do you mean? Nothing was attached.

You probably run your Jira on different DB than MySQL. You might need to change some parts to work it on different DB. 

[Grzegorz Ziolek]

I'm using mysql.

The error I get is:

[42000][1055] Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'jiradb.fcsit.FIELDCONFIGSCHEME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

[Radek Janata]

Your MySQL is configured differently. You might want to disable ONLY_FULL_GROUP_BY -- see https://stackoverflow.com/questions/23921117/disable-only-full-group-by.

Or try to adjust the GROUP BY fields with all the fields from SELECT:

 GROUP BY fcsit.FIELDCONFIGSCHEME, fcsit.FIELDCONFIGURATION, fcs.configname, oc.FIELDCONFIG) its ON its.FIELDCONFIGSCHEME = cc.FIELDCONFIGSCHEME

[Grzegorz Ziolek]

@Radek Janata 

Thanks, this helped :) 

One more question, when running this query I get all projects except those using  Default issue type scheme. Looks like those projects are not in configurationcontext table.

Any idea what can be done in this situation?

[Radek Janata]

@Grzegorz Ziolek, good point! This is fixed query:

SELECT
  p.pkey AS 'Project Key',
  p.pname AS 'Project Name',
  its.configname AS 'Issue Type Scheme',
  its.issuetypes AS 'Issue Types'
FROM project p
LEFT JOIN configurationcontext cc ON p.ID = cc.PROJECT AND cc.customfield = 'issuetype'
LEFT JOIN (
  SELECT
    fcsit.FIELDCONFIGSCHEME,
    fcsit.FIELDCONFIGURATION, 
    fcs.configname,
    GROUP_CONCAT(it.pname ORDER BY oc.SEQUENCE SEPARATOR ', ') AS issuetypes
  FROM optionconfiguration oc
  LEFT JOIN issuetype it ON it.ID = oc.OPTIONID
  LEFT JOIN fieldconfigschemeissuetype fcsit ON fcsit.FIELDCONFIGURATION = oc.FIELDCONFIG
  LEFT JOIN fieldconfigscheme fcs ON fcs.ID = fcsit.FIELDCONFIGSCHEME
  GROUP BY fcsit.FIELDCONFIGSCHEME, fcsit.FIELDCONFIGURATION, fcs.configname, oc.FIELDCONFIG) its ON its.FIELDCONFIGSCHEME = cc.FIELDCONFIGSCHEME OR cc.FIELDCONFIGSCHEME IS NULL AND its.FIELDCONFIGSCHEME = 10000
-- HAVING its.issuetypes RLIKE 'Support Ticket'
ORDER BY p.pkey
;

And this was removed:

WHERE cc.customfield = 'issuetype' 

[Hack Vogel]

The following SQL (SQL Server) will retrieve the name of the Issue Types Scheme used on project(s):

SELECT prj.pkey as 'Project Key', prj.pname as 'Project Name'
,(CASE -- get the Issue Types scheme
WHEN EXISTS(select cc.FIELDCONFIGSCHEME from [configurationcontext] cc where cc.customfield = 'issuetype' AND cc.PROJECT = prj.ID) THEN
(select top 1 fc.configname from [fieldconfigscheme] fc
where fc.ID = ( select top 1 cc.FIELDCONFIGSCHEME from [configurationcontext] cc
where cc.customfield = 'issuetype' AND cc.PROJECT = prj.ID) )
ELSE
(select fc.configname from [fieldconfigscheme] fc
where fc.ID = 10000) -- Default Issue Type Scheme
END) as 'Issue Types Scheme Name'
FROM [project] prj
WHERE prj.pkey = 'your desired project key'