Priority Scheme in the database

[Adam_G]

I am trying to discover which projects are using the Default Priority Scheme. Unlike with Default Issue Types or Permission Schemes, you are unable to get a list of projects in the UI which are utilizing the Default Priority Scheme (at least that I have found.) Can anyone point me to a SQL query that I could run to get me the projects utilizing the Default Priority scheme similar to the below:

SELECT p.pkey AS ProjectKey,
 itss.name AS IssueTypeScreenScheme
FROM project p JOIN nodeassociation na ON p.id = na.source_node_id
 JOIN IssueTypeScreenScheme itss ON itss.id=na.sink_node_id
AND sink_node_entity = 'IssueTypeScreenScheme'
AND itss.name = 'Default Issue Type Screen Scheme'

[Earl McCutcheon · Accepted solution]

Hello Adam,

I believe this will do the trick for you:

SELECT p.pkey AS "ProjectKey",
 f.configname AS "Priority Scheme"
FROM project p,
 configurationcontext c,
 fieldconfigscheme f
WHERE c.project = p.id 
 AND f.fieldid='priority' 
 AND f.id = c.fieldconfigscheme
 GROUP BY f.configname, p.pkey
 ORDER BY "ProjectKey";

The Query is a bit rough but for a little background on the tables to help refine this a bit, The "fieldconfigurationscheme" table houses the priority scheme name and the "fieldid" column with a value of "priority" is the identifier that this is linked to the priority scheme:

select * from fieldconfigscheme where fieldid='priority';

Then the "configurationcontext" table "project" column is the mapping between the scheme and the project:

select * from configurationcontext where fieldconfigscheme in (select id from fieldconfigscheme where fieldid='priority'); 

-Earl

[Alexandre Machado]

Hi !!

How I can found the priorities of each priority scheme ?

Thanks,

Alexandre

[Rahul Savaikar]

You may directly check the priority table.

select * from priority;

 

[Yevgen Lasman]

@Alexandre Machado actually priorities and their schemes are linked through the "optionconfiguration" table. Below is an example of SQL for Postgres

select fcs.configname, string_agg(p.pname, ', ' order by p.pname)
from optionconfiguration oc
right join priority p on oc.optionid = p.id
right join fieldconfigscheme fcs on oc.fieldconfig = fcs.id
where oc.fieldid='priority'
group by fcs.configname
order by fcs.configname;