I’m currently working on integration of YARG into our Django-based web application (so, not a CUBA-related question really). YARG is called from command line.
My question is - how do I make a report with subsections (similar to Bookstore report for example), using only one SQL query?
In other words, Bookstore report has two bands - Shop and its sub-band Book.
SQL query for Shop fetches the list of book shops (“id”, “name”, “address”) and for each record found:
inserts its named range of cells (“Shop”) from the template into the report;
inserts Book sub-band, which results in -
2.1) Book SQL query being executed (“author”, “name”, “price”, “count”);
2.2) for each record in that query, “Book” named range is inserted into the report.
So Book query is executed as many times as there are shops, which is wasteful.
Let’s suppose I wrote an SQL that fetches the following fields:
“id”, “name”, “address” – these refer to the shop
"author", “name”, “price”, “count” – and these - to the book
Each record from this set would represent a certain book in a certain shop. Just one SQL query instead of N+1.
How would I create the same report from this query? I can imagine making a “master band” with the query and using Groovy queries in the sub-bands to fetch data from it, BUT:
the master band is still a band and it would be added to my report and I don’t want that;
the seb-bands would be invoked for each record in the master band (which means, for each shopbook combination), having access to that record only, and not to the whole dataset. And in order to, say, extract the list of shops from the bookshop dataset you have to iterate over the whole set, I’m afraid.
Hello.
First of all, I have to say that you point is right - the usual way to create hierarchical bands is creation of sub-queries, and it leads to N+1 queries(for 2 level bands).
But you can workaround it using groovy scripts.
You can run the SQL query from Groovy script placed in Root band, handle the results in the same script and put the results into params map. You can place list of shops and books in any data structure you want. Then in Shop band and Book band you can just collect (using Groovy scripts) necessary data from the data structure which you have put to the params. In this case, you would not have excessive master band (because Root band is always there), and the data would be organized in the appropriate structure.
I know that the above solution is not ideal, but for now there is only one way to avoid N+1 sql queries - to manage data manually, using groovy scripts.
We wil try introduce better solution in future.
Thank you for your question.
Could you please help me by giving an example of Groovy code running an SQL query with report parameters, as in
"-PStartDate=01/01/2015 00:00" and then iterating over the resulting dataset?
Also, how would I put the results into params map for the sub-bands to extract? Are you saying there’s literally a map called “params”, to which I can add elements by doing
params.add([‘Key’: ‘Value’])
?
All the examples I can find on the Internet typically start with establishing a DB connection - def sql = Sql.newInstance (which seems logical). However, since Groovy code we’re talking about is supposed to be executed by another application which already has DB connection established by that point, I thought that maybe I can just get that object from the parent application?
There’s a slight problem with Bookstore2 - it doesn’t work.
Looks like “Root” query is not executed when it is ran from the rootBand.
If, however, I create another band in rootBand, put Shop band and “Root” query into it, I get the following error:
Script1.groovy: 6: unable to resolve class com.haulmont.yarg.console.DatasourceHolder
@ line 6, column 25.
import com.haulmont.yarg.console.DatasourceHolder
^
Please disregard the previous comment - it was my mistake.
I thought that Bookstore2 exampe was all that was added to YARG repository, when, in fact, the whole library had to be rebuilt.