How to get the Mapping between Workflow, Issue Type and project?

[TAGNAOUTI Khaoula]

Hello everybody,

There is any database table in JIRA has the mapping information between workflow, Issue Type and project?

I checked the database but I didn't find a table with association.

Could you please help me for this issue?

[Sebastian Krzewiński · Accepted solution]

Hi @TAGNAOUTI Khaoula 

Mapping between project and workflow scheme (and other schemes) you can find in nodeassociation table. Then (knowing workflow scheme id used in project) you can find workflows assigned to issue types in this specific workflow scheme in workflowschemeentity table.

Similar topic: https://community.atlassian.com/t5/Jira-questions/the-relations-between-project-and-workflows-in-the-database/qaq-p/11249

Regards,

Seba

[TAGNAOUTI Khaoula]

Hi @Sebastian Krzewiński 

Thank you for your return.

This query shows the "workflow scheme" information not the "workflow" information.

However, it doesn't show the Issuetype information.

Regads,

AP

[Sebastian Krzewiński]

What query?

[TAGNAOUTI Khaoula]

The query indicated in the link above:

SELECT *

FROM PROJECT, NODEASSOCIATION, WORKFLOWSCHEME

WHERE PROJECT.ID = NODEASSOCIATION.SOURCE_NODE_ID

AND NODEASSOCIATION.SINK_NODE_ID = WORKFLOWSCHEME.ID

[Sebastian Krzewiński]

Hi @TAGNAOUTI Khaoula 

Query from article was not good.

Try this one:

select n.source_node_id, w.scheme, n.sink_node_id, w.workflow, w.issuetype from workflowschemeentity w 
join nodeassociation n on n.sink_node_id=w.scheme
where n.source_node_id=<project_id> and n.sink_node_entity like 'WorkflowScheme'

Now you need to know only project id and put in query.

Please let me know if it works for you.

Regards,

Seba

[TAGNAOUTI Khaoula]

@Sebastian Krzewiński  It doesn't work, this the error msg that I receive:

00936. 00000 - "missing expression"

[Sebastian Krzewiński]

What database do you have?

This query works in PSQL. You can easily change it if it is needed in other database.

[TAGNAOUTI Khaoula]

Oracle

[TAGNAOUTI Khaoula]

@Sebastian Krzewiński It works! Thank you very much for your help!

Regads,

AP

[Sebastian Krzewiński]

@TAGNAOUTI KhaoulaI'm glad that it work :)

If you don't might please accept my answer so other users can see that this query is working ;)

[TAGNAOUTI Khaoula]

Finally, I realized that I need a query for "issue status" instead of "issue type".

I oponed another topic if you could help me plz.

[Sebastian Krzewiński]

Sure :)

Please paste link to new topic.

[HaRsHaS_ V]

@Sebastian Krzewiński , Thanks for the info..I see that the query you gave does not provide the issuetype name. I used all columns, no luck. Could you please help me relating workflows and issuetypes?

[Sebastian Krzewiński]

@HaRsHaS_ VSure!

select n.source_node_id, w.scheme, n.sink_node_id, w.workflow, w.issuetype, isstype.pname from workflowschemeentity w 
join nodeassociation n on n.sink_node_id=w.scheme
join issuetype isstype on w.issuetype=isstype.id
where n.source_node_id=<project_id> and n.sink_node_entity like 'WorkflowScheme'

[HaRsHaS_ V]

@Sebastian Krzewiński Thanks for the reply, is there any way we can capture the unassigned issutypes? Because I ve tried this query and the unassigned types seem to be left out..I checked few tables but no luck..

Any help is highly appreciated..and I have  questions on 2 other queries..if you have time to answer.

[Sebastian Krzewiński]

What do you mean with unassigned issue types?

[HaRsHaS_ V]

@Sebastian Krzewiński , I meant all the issuetypes that are not associated workflow, so they tend to use the Default Workflow.