Let's say i have:
Let's also say I'm not super confident in my sql skills (my jql is way better). Since project A is the only project using this importance field, I'm doing something like:
SELECT `Customfieldoption`.`CUSTOMFIELD` AS `CUSTOMFIELD`, `Customfieldoption`.`customvalue` AS `Customvalue` FROM `customfieldoption` AS `Customfieldoption` WHERE ((`Customfieldoption`.`CUSTOMFIELD` = 12321))
and what I end up with is three rows for each issue--it's showing me ALL OPTIONS rather than the selected option. Is this:
SQL is absolutely the best way to get JIRA data :) You just need to know a few table facts:
To learn the JIRA tables behind a particular operation, a useful trick is to generate a text dump of the database before the operation (e.g. editing a customfield) and after, then diff the two text dumps. See https://www.redradishtech.com/display/KB/Using+database+diffs+to+see+what+JIRA+is+doing
Depends on the context of what you're doing, but there's two basic ones.
If you're working inside JIRA, in an add-on, then the API has several simple "give me custom field value from issue X" options.
From outside JIRA, a single REST call fetches the whole issue in one step (and if you make it a bit longer, you can ask for a single field)
SQL is an utter disaster for reporting in JIRA. Don't do it.
SQL is absolutely the worst way to get to JIRA data, and should not be used in this way. As a simple example, you now need to join over 40 tables (many of them more than once) just to get the data for a single issue. Do not use SQL to do it.
(And, please, when posting questions, drop the use of "as", it does nothing more than save you a bit of typing while making your SQL a lot harder to read)
So, moving on, you don't say what question you are trying to answer with this query. What are you looking for here?
Apologies for the illegibility of my sql. As noted, sql is not one of my strong points.
At the micro level, I'm looking for a list of project A issues with their corresponding "importance" value. At a higher level, I'm hoping to use the query in Chartio to present a stacked bar chart of issues created monthly, the bar stacks representing "importance."
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!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG
We're bringing product updates and pro tips on teamwork to ten cities around the world.Save your spot