nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

database - issue type of a project

Cláudia Silva

hi,

how can i know the issue types that belong to a project? if i use the table nodeassociation i know the issue type screen of project, but not issue types. where is the table that relates "issuetype" whith "issuetypescreenscheme"?

SELECT

proj.pname AS project,

issc.name AS issuetypescreen

FROM

project proj

JOIN

nodeassociation na

proj.id = na.source_node_id

JOIN

issuetypescreenscheme issc

na.sink_node_id = issc.id

AND sink_node_entity = 'IssueTypeScreenScheme';

thanks,

Accepted answers

Vidic Florjan

Link to Jira SQl examples: Example SQL queries for JIRA

Link to Get Issue Types for project example: https://confluence.atlassian.com/display/JIRACOM/Example+SQL+queries+for+JIRA#ExampleSQLqueriesforJIRA-GetIssueTypesforproject

SELECT IT.id, IT.pname
FROM configurationcontext               CC
LEFT OUTER JOIN optionconfiguration     OC ON OC.fieldconfig = CC.fieldconfigscheme
LEFT OUTER JOIN issuetype               IT ON IT.id = OC.optionid
WHERE CC.project = 10000

10000 is ID of the project

All comments

cofarrell

I hope you don't mind but I updated to the tags to be JIRA instead of Stash which is what I assume you meant.

Vidic Florjan

Link to Jira SQl examples: Example SQL queries for JIRA

Link to Get Issue Types for project example: https://confluence.atlassian.com/display/JIRACOM/Example+SQL+queries+for+JIRA#ExampleSQLqueriesforJIRA-GetIssueTypesforproject

SELECT IT.id, IT.pname
FROM configurationcontext               CC
LEFT OUTER JOIN optionconfiguration     OC ON OC.fieldconfig = CC.fieldconfigscheme
LEFT OUTER JOIN issuetype               IT ON IT.id = OC.optionid
WHERE CC.project = 10000

10000 is ID of the project

Cláudia Silva

thank you so much.

but i have been reading the documentation and i read this:

Notes about working with the JIRA database:

(warning) Please Note: Direct database queries are not recommended in JIRA. Instead, we recommend adding or modifying data via JIRA's REST APIs, JIRA RPC Services (deprecated) or Jelly Tags.

But, my my goal is only acess to information, not modify. It is recomended use the res api (for example) for this?

With the rest api can i acess an issue types of a project?

best regards

ana mendes

Gregory Kneller

Ana, please do not worry about this note from Atlassian. All API are very poor, and Atlassian changes focus often from one API to another. For example, they drop Jelly and try to imporve REST but it is still not very functional. Use database, to read or to write (but test everyting first in a non-production instance)

Jeff Louwerse

still works today but you might want to add this to the where clause

and CC.customfield = 'issuetype'

Ipsita Dash_226ssg

Hi,

Below query gives me all the projects which have a particular issue type.

Give a try.

SELECT DISTINCT P.pname, P.pkey, P.lead AS project
FROM project as P
INNER JOIN jiraissue as J on J.PROJECT = P.ID
INNER JOIN issuetype as I on I.ID = J.issuetype
WHERE J.issuetype= 10300

Thanks,

Ipsita Dash

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

sql

jira

moderators

database