Postgresql report query with CF

Question

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)

Nic Brough -Adaptavist- · Answer

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.

krzysztofzarebski · Answer

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- · Answer

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)