equivalent sql for Advanced searching - functions reference

Xavier_v2dw4s

Dear support,

how we can queries database to get ticket with approvals or pending approach, as explained on Advanced searching options.

My needs is to provide some simple report with equivalent of JQL:

project = "IT Service Desk" AND issuetype in ("xxx") AND Approvals = myPending() ORDER BY created DESC

and also

 

project = "IT Service Desk" AND issuetype in ("xxx") AND Approvals = approvals() ORDER BY created DESC (or myApprovals)

 

as dashboard is not available for customer users (those users have an account in jira, but are not member of service desk application..)

I would like to "rewrite" those JQL by querying sql database.
is it possible ?

Thanks in advance.

regards,

Xavier

 

Nic Brough -Adaptavist-

It is possible, but it is painful, and can easily cause performance issues.

Jira's database is absolutely not built or intended for any direct reporting.  The JQL you've given us as an example requires 8 joins, just for a single issue type, and that's probably an underestimate as I've only looked at the basics of the function you've used.

I would very strongly recommend using a reporting tool that understands Jira data, or better, the REST API (which can run the JQL you want to "translate" without hitting performance any more than a dashboard would)