how to join agile board and related tickets in JIRA database

Brian Hayden August 27, 2014

Hi,

I am new to JIRA. I would like to be able to list all boards available along with the tickets in each of them. I have already checked JIRA database. There is a table called JIRAISSUE which contains all the tickets and AO_60DB71_RAPIDVIEW table which has all the boards in this. That's the final stage I have got so far. Any idea how can I actually join them by writing an SQL query, if possible? JIRA database is quite complicated, what I am looking to find out is, if there is a table which contains references to both tables because JIRAISSUE and RAPIDVIEW tables have no direct interactions.Ultimately, I need to find out what field can I use in order to get the result I need. I have checking all tables/fields and also not sure how the database structure is.

If there are any other suggestions, Thanks in advance.

1 answer

0 votes
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 27, 2014

There is no direct relationship.

A board works off a JQL query. You'd need to find and replicate that query in SQL to be able to say "this issue is on this board". In other words, read the Board table to find out what the filter is. Look that up in "searchrequest", read the query in there, converting each clause and join into it's SQL equivalent, and execute that.

Bear in mind if someone is being "clever" with a filter and using ANY functions, you need to code for them as well. You'll need to write SQL translations for the 18ish system fields, something to handle the 12ish custom field types (plus any field types created by any plugin you happen to install) and the 35 or so JQL functions (and again, any added by other plugins)

In short, forget SQL, it's the wrong approach (it usually is when questioning Atlassian products). Start with the original question: Why are you trying to list boards and their contents?

Brian Hayden August 27, 2014

Thanks for your answer. I am doing my internship at the company where I was assigned a task to provide a report of issues on a regular basis. Basically, what I have to do is, to send a report of specific boards to peoject managers. Each board has a number of tickets in it. The report will contain the board name, list of tickets and their current statuses'.

There is already a reporter application (written in Delphi XE) in which I am just going to create a new unit to do my part. In there, I have to retrieve these values by connecting to Oracle Database, creating tables and sending as an email.

JIRA database is not great tbh, so it took me a while to find these tables. As I mentioned above I can get these values seperataly from those tables. However, I couldn't find any third table/field so that I could write a join statement. I was just looking for an efficient way of fulfilling this task. I hope I wrote it clear enough.

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 27, 2014

Yes, your requirement is very clear. It's not going to work if you try to do it this way, but it is clear.

The Jira database is actually pretty well designed. For being the storage engine behind Jira. The main problem here is some dodgy reporter application that can't and won't keep up with the data structure that is optimised for using Jira.

I'm afraid my answer doesn't change - to extract this data, you are going to need to re-code the entirity of Jira's JQL language in order to parse the queries you find for each board correctly.

As I said before, forget doing this with SQL, it's a non-starter. There is a REALLY easy way to do this though.

For each board, get the filter name. Go to "manage filters" and find it on the list. To the right, click "subscribe". Fill in the details (who to bother and when). Job done.

Brian Hayden August 28, 2014

Thanks, your comments were very useful. I have figured out by making a slight modification to the requirements. Now, I list all the issues within specific sprint but the only blocker is I can't join RAPID_VIEW and SPRINT table to specify which board the sprint belongs to. I was expecting SPRINT table to have a rapid_view_id field as well referencing to the original table but apparently there is no such thing.

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 28, 2014

Technically, you can get sprints on multiple boards, so no, there won't be an easy mapping...

Brian Hayden August 28, 2014

It's not going to easy anyways :) However, I have seen this guy writing the query to retrieve RAPID_VIEW_ID from SPRINT table.

https://answers.atlassian.com/questions/224745/get-issues-by-sprint-from-database

This field doesnt actually exist in the jira db that I have a connection. It's another challange to find a way around if this implementation is possible.

Suggest an answer

Log in or Sign up to answer