Postgresql report query with CF

[krzysztofzarebski]

I know that is more db postgresql question, but I can't find solution on web. I create report view for customer, but there is missing issues (rows), becouse there is none rows in customfieldvalue table.

---

SELECT DISTINCT
CONCAT(p.pkey, '-', ji.issuenum) AS key,
ji.summary,
iss.pname AS status,
r.pname AS resolution,
pr.pname AS priority,
ji.created,
ji.updated,
CONCAT(cu.first_name, ' ', cu.last_name) AS osobazglaszajaca,
cfv.stringvalue AS firma,
cfv2.numbervalue AS iloscreklamowanychproduktow
,cfo.customvalue AS kategoriapwg
,cfo1.customvalue AS kategoriadefektu
FROM ((((((((((((jiraissue ji
JOIN project p
ON ((p.id = ji.project)))
JOIN issuestatus iss
ON (((ji.issuestatus) :: TEXT = (iss.id) :: TEXT)))
JOIN resolution r
ON (((ji.resolution) :: TEXT = (r.id) :: TEXT)))
JOIN priority pr
ON (((pr.id) :: TEXT = (ji.priority) :: TEXT)))
JOIN app_user au
ON (((au.user_key) :: TEXT = (ji.reporter) :: TEXT)))
JOIN cwd_user cu
ON ((cu.id = au.id)))
JOIN customfieldvalue cfv
ON ((cfv.issue = ji.id)))
JOIN customfieldvalue cfv2
ON ((cfv2.issue = ji.id)))
JOIN customfieldvalue cfv3
ON ((cfv3.issue = ji.id)))
JOIN customfieldoption cfo
ON ((cfo.id = (SELECT cfv3.stringvalue ::DECIMAL))))
JOIN customfieldvalue cfv4
ON ((cfv4.issue = ji.id)))
JOIN customfieldoption cfo1
ON ((cfo1.id = (SELECT cfv4.stringvalue ::DECIMAL))))
JOIN customfield cf
ON cf.id = cfv4.customfield
WHERE ((ji.project = (10200) :: NUMERIC)
and (cfv.customfield = (10514) :: NUMERIC) 
AND (cfv2.customfield = (10507) :: NUMERIC) 
AND (cfv3.customfield = (10500) :: NUMERIC) 
AND (cfv4.customfield = (10510) :: NUMERIC) 

[Radek Dostál]

Afaik that's not going to be easy without some wizardry, either through subqueries or temporary tables. Can't say I have any clear suggestion from the top of my head without doing some tinkering.

While this is not the topic, why go through the pain and sweat with db? All of the data you're getting can be easily found through either issue navigator as columns (csv/etc export), or through REST. Database sounds like an unnecessary amount of masochism.

You could just do an issue search through REST, including the fields you want to return, and then you could parse the JSONArray into any other format - and this would be more along the "expected" use of the app. If the report is a plugin/java then also - easier to use the search interface than do direct db queries.

https://docs.atlassian.com/software/jira/docs/api/REST/8.22.6/#search

[krzysztofzarebski]

Guys, thank you a lot that you found piece of time to dive into my problem. The case is very easy, my customer wants to connect to db view to show this data in other system like Grafana. Some pie chart, and so on. This query contains simple joins with main table jiraissue. But the problem is in the custom field tables. I mean customfieldvalue, customfield and customfieldoption. Some custom fields are not obligatory, so there is no row in customfieldvalue table, and the result is that full row is not available in this view.

[Nic Brough -Adaptavist-]

Yes, the way the database is used as a data store, and the logic done in the application means that you're never going to be able to write sane, performant, SQL that is of any use for reporting in all but the most simple of cases.

If this is to go into Grafana, I'd go a different route, one of

• Write a proper extractor that uses the REST interface to read the data you want out and process it into the shape you need, and make it available to Grafana
• Write an app you can plug into Jira that provides the direct links Grafana might use (I don't know if this is possible, I only know the basics of Grafana - end user, not really got into anything more difficult than basic connection and building reports)
• Write an app you can plug into Grafana to do the work for you (note that Grafana already has one for Jira Cloud, so it's definitely possible - that uses the REST interface to draw data out)
• Maybe look at a different extraction process.  I've seen a client who wrote a simple Prometheus exporter for Jira, which could then feed into Grafana.  There are a couple of these available already, but the ones you'll find with an internet search are about monitoring Jira services, not data.  My client had written one that extracted changes of fields on issues, so people could build graphs about how issues changed over time.  

[Nic Brough -Adaptavist-]

This is not a nice thing to try to do with the database, I can't quite see what your query is trying to do.

What is the question you are trying to answer with this query?  It looks like it might be a "show me some issues matching a question, and some of their field content".  Is that it?  Could you give us the question (and ideally the reason behind why it is being asked)

[krzysztofzarebski]