How to convert JQL to MySQL

I've wrote the following JQL and I need to get a same result as this from a MySQL query (our Jira Service Desk's database is MySQL).

project = "ITZone Software Support" and created >= 2017-10-1 and created <= 2017-12-31 and Organizations = ("Mcs Coca Cola", "Mcs Estates", "Mcs Holding", "Mcs Property HRM", "Mcs International")

The tricky part is the Organizations, the jiraissue table doesn't have a field named Organization or Organizations, so I tried the following MySQL:

select project.pkey, jiraissue.issuenum, jiraissue.CREATED, AO_54307E_ORGANIZATION.NAME, jiraissue.REPORTER, issuetype.pname, priority.pname,
issuestatus.pname, jiraissue.SUMMARY, round(jiraissue.TIMESPENT/3600) from jiraissue
inner join issuestatus on jiraissue.issuestatus = issuestatus.id
inner join issuetype on jiraissue.issuetype = issuetype.id
inner join priority on jiraissue.priority = priority.id
inner join project on jiraissue.project = project.id
inner join AO_54307E_ORGANIZATION_MEMBER on jiraissue.REPORTER = AO_54307E_ORGANIZATION_MEMBER.USER_KEY
inner join AO_54307E_ORGANIZATION on AO_54307E_ORGANIZATION_MEMBER.ORGANIZATION_ID = AO_54307E_ORGANIZATION.ID
where jiraissue.project = 10000 and jiraissue.created >= '2017-10-1' and jiraissue.created <= '2017-12-31';

But I'm not getting the result I need because our team often create issues on behalf of our customers, so such issues has one of our team members as the reporter but the Organization would be one of our customer companies, thus the above query won't work, Admin/Agent users are not included in the AO_54307E_ORGANIZATION_MEMBER table. So I wonder how the above JQL works? how does it filter by Organization?

1 answer

0 vote
Alexey Matveev Community Champion Jan 22, 2018

Hello,

Why would you need to do it with SQL? You could receive the same data with JIRA REST API. There is a REST API method which lets you receive data from Jira by passing a JQL query:

https://developer.atlassian.com/cloud/jira/platform/rest/#api-api-2-search-get

Hello,

Thanks for a prompt reply, I need to do it in MySQL because I'm trying to generate a Tableau report directly from our Jira database.

Please, don't try to report off a Jira database.  Jira's database is not used as a database, it is a data store and it absolutely the opposite of anything you might design for reporting. 

Consider the JQL "issuekey = ABC-123" - if this is an issue in a scrum project with a couple of custom fields and you're asking for just the data on the issue view screen, you can easily be looking at 40+ joins in SQL.

Please, use the REST API.

Suggest an answer

Log in or Join to answer
Community showcase
Emilee Spencer
Published yesterday in Marketplace Apps

Marketplace Spotlight: DeepAffects

Hello Atlassian Community! My name is Emilee, and I’m a Product Marketing Manager for the Marketplace team. Starting with this post, I'm kicking off a monthly series of Spotlights to highlight Ma...

50 views 0 3
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot