equivalent sql for Advanced searching - functions reference

[Xavier]

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- · Accepted solution]

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)

[Xavier]

Dear Nic,

thanks for your quick answer. I understand using database is not real recommended (but it really help me if you can provide such SQL query, by PM if possible).

But how I can retrieve the result of JQL via REST on Confluence ? Is it possible ? And what about connected "customer" users that didn't have access to Jira, only Jira SD ? are they able to see the Confluence page where REST get data ?

regards,
Xavier

[Nic Brough -Adaptavist-]

Anything can run JQL - see https://developer.atlassian.com/server/jira/platform/jira-rest-api-examples/#searching-for-issues-examples - you can almost paste your JQL into there for a working call.

To get it into Confluence, you will need to find something that can make the call, and more importantly, format it usefully.  You will need to look for something that can obscure what you are doing as well, as it will need to connect as a Jira user who can see all the returned issues, and you don't want your customers seeing the details of that.