Concatenating or formatting strings from m-to-n relationship in a report

eythian · November 22, 2022, 8:17pm

I’m building a small thing that I want to produce nicely formatted (for printing) reports. There is one aspect I’m stuck on.

I have a many-to-many relationship, with a join table. As an example, books to authors.

My goal is have a field in the report that looks like:

Title of book
by John Doe, Bill Smith, Tom Jones

(aside: extra nice if I can have John Doe, Bill Smith, and Tom Jones, but not essential.)

My tables are:

book:

id
title

author:

id
name

rel_book_author:

book_id
author_id

Ideally, in SQL with a query I’d be able to produce a table that is:

book_details

title
authors (which looks like “John Doe, Bill Smith, Tom Jones”, sourced from the author table.)

I attempted to use GROUP_CONCAT but hsqldb is too old. I can’t see any other way of doing this at the query/SQL level (though would love suggestions.)

So perhaps I can do it at the report level. Here I’m a bit stuck, I can see how I might do something like:

Title of Book
* John Doe
* Bill Smith
* Tom Jones

or similar, by doing a join in the query that gives me repeated rows for each author and then grouping by title (or ID or whatever), but not how to format it properly.

I am using the inbuilt database on LibreOffice Base 7.4. This DB choice is something of a constraint as I’m building this for someone else, and just want to be able to hand them a file that works and not have to set up anything else. On the other hand if someone can recommend an option that is very easy to set up on a Windows laptop, that might also work.

Any help? Thanks in advance.

Villeroy · November 22, 2022, 8:45pm

Copy your Base document to a dedicated directory.
Add a subdirectory “driver”.
Download HSQL 2.4.1 and extract /lib/hsqldb.jar to subdirectory “driver”.
Download my installer document https://forum.openoffice.org/en/forum/download/file.php?id=35569 and click the button.
Open your Base document and call:
Tools>Macros>Execute>“My Macros”>pyDBA>ExtractHSQLDB>Main
The statusbar changes to something like jdbc:hsqldb:file:/path/database/DB_Name;…
Test if everything works as before .

Add your GROUP_CONCAT query.

RobertG · November 22, 2022, 8:53pm

First solution:
Tools → Options → LibreOffice → Advanced → Optional Features → Enable experimental Features
With experimental features ‘on’ you could create a internal Firebird database. You could set it ‘off’ after creating this database.
Internal Firebird will offer the function List(). This will work like GROUP_CONCAT

Second solution:
Internal HSQLDB and a report. The query should show 3 rows for the title of the book. Title is the same, but authors will differ. Title will be the group in the report. Authors will be one author-field in section “Detail” of the report. “Detail” will be repeated for every row with the same title of a book.

eythian · November 22, 2022, 9:41pm

Thanks, that seems to have got me further.

A note: I’m developing this in Ubuntu and I had to install libreoffice-script-provider-python before LO could see the script, after that it appeared to run fine and I can see my data.

However attempting to use GROUP_CONCAT results in this error, when doing a simple test:

Unexpected token: GROUP_CONCAT in statement 
[SELECT product_id, GROUP_CONCAT(producer_id) AS concatted 
FROM "rel_product_producer" WHERE "product_id" = 1 
GROUP BY product_id] at ./connectivity/source/drivers/jdbc/Object.cxx:173

I have selected the “Run SQL command directly” button in the query design screen I’m testing in, but it causes no change in behaviour.

Villeroy · November 22, 2022, 11:27pm

SELECT "product_id", GROUP_CONCAT("producer_id") AS "concatted"
FROM "rel_product_producer" 
WHERE "product_id" = 1
GROUP BY  "product_id"

Confirm HSQL version:

SELECT DATABASE_VERSION( ) AS "HSQL_VERSION" FROM "INFORMATION_SCHEMA"."SYSTEM_TABLES" WHERE "TABLE_NAME" = 'ROUTINES'

eythian · November 23, 2022, 12:50pm

Same thing:

2: Unexpected token: DATABASE_VERSION in statement [SELECT DATABASE_VERSION(] at ./connectivity/source/drivers/jdbc/Object.cxx:173

however, this jogged something in my brain and I went to

Tools → Options → Advanced → Class Path… → Add Archive

and browsed to where I had saved the hsqldb.jar file. Now,

SELECT DATABASE_VERSION( ) AS "HSQL_VERSION" FROM "INFORMATION_SCHEMA"."SYSTEM_TABLES" WHERE "TABLE_NAME" = 'ROUTINES'
2.4.1,

(dunno why the trailing comma)

It’s also doing the right thing with GROUP_CONCAT, so I think you’ve solved my problem! Thanks very much!

cpb · November 23, 2022, 2:16pm

hello eythian,

I know nothing about Villeroys HSQL auto setup but you should remove the CLASS PATH you set otherwise any embedded HSQLDBs you open will be corrupted.
you made the right decision in choosing to go for a split HSQLDB.
I show this code which works with the embedded HSQLDB simply to demonstrate that it’s possible.

select
	b."title",
	t.c0 ||							--max 5 authors
	coalesce(', ' || t.c1, '') ||	--easy to add more c5,c6 etc.
	coalesce(', ' || t.c2, '') ||
	coalesce(', ' || t.c3, '') ||
	coalesce(', ' || t.c4, '')		--final line no pipes
	"authors"
from
(
	select
		a."book_id",
		max(case when a.slot = 0 then a."name" end) c0,	--max 5 authors
		max(case when a.slot = 1 then a."name" end) c1,	--easy to add more c5, c6 etc.
		max(case when a.slot = 2 then a."name" end) c2,
		max(case when a.slot = 3 then a."name" end) c3,
		max(case when a.slot = 4 then a."name" end) c4
	from
	(
		select
			t1."book_id", t1."name", count(t2."book_id") slot
		from
		(
			select ba."book_id", n."name"
			from
				"rel_book_author" ba
			join
				"author" n
				on ba."author_id" = n."id"
		) t1
		left join
		(
			select ba."book_id", n."name"
			from
				"rel_book_author" ba
			join
				"author" n
				on ba."author_id" = n."id"
		) t2
			on t1."book_id" = t2."book_id" and t1."name" > t2."name"

		group by t1."book_id", t1."name"
	) a
	group by a."book_id"
) t

join
	"book" b
	on t."book_id" = b."id"

order by "title"

eythian · November 23, 2022, 2:54pm

As far as I can tell, Villeroy’s auto-setup basically converts embedded into split. There might be a more proper way to do the classpath so it only applies to this document, and I’d like to know how if so. For now this’ll get me moving, but it’d be best to do it right.

As for your example, this implies a somewhat denormalised database structure, which is something I’d like to avoid if possible.

I’m actually surprised that this isn’t apparently easy to do at the reporting level, as it sounds like a common thing to want.

Villeroy · November 23, 2022, 3:01pm

and points the current database to any given driver/hsqldb.jar without using the class path. Using the class path method destroys any embedded HSQL database. When you open it, it will be successfully converted but can not be opened a second time.

The given hsqldb.jar should be version 2.4.1 because later versions are incompatible with HSQL 1.8.
If you do not provide driver/hsqldb.jar, the macro points to the driver version 1.8 which is shipped with the office suite.

Demo database with a query “qGroupConcat”: FilterData-HSQL-2.4
Extract zip archive to trusted directory, open the Base document. A Basic macro will connect the document to the right driver and database.

eythian · November 23, 2022, 4:55pm

Curious, I had already extracted the correct jar file in the driver subdirectory (where that directory is at the same level as the odb file.)

The demo database doesn’t work for me, with the class path unset. It gives me two messages, the first is a “successfully connected to database and driver”, and the second is when I try to access a table:

The connection to the data source "FilterData" could not be established.

SQL Status: S1000
Error code: -78

error in script file line: 1 Unexpected token UNIQUE, requires COLLATION in statement [SET DATABASE UNIQUE] at ./connectivity/source/drivers/jdbc/Object.cxx:173

This error is exactly what I see when I remove the classpath from my configuration also.

Villeroy · November 23, 2022, 6:13pm

This is what you should see when opening my demo for the first time. I tried successfully two times before uploading.
FreeHSQL_Success

cpb · November 24, 2022, 1:22am

@eythian said:
this implies a somewhat denormalised database structure

it absolutely does not.
if you google ‘conditional aggregation’ then it may open your eyes.
.
things are always as they are and not as one may naively wish they were.
.
how many different databases can you name either commercial or open source which can pivot more than one field.
.
successful database development requires application, accumulated knowledge and at least a moderate degree of intelligence.

eythian · November 24, 2022, 2:23pm

Hi, thanks for the continued assistance.

I do see that message, but the setting introduced doesn’t seem to apply for some reason.

I’ve made a short screencast to show you what I see:

Villeroy · November 24, 2022, 2:36pm

I don’t really understand the problem, but you can safely delete the first line from database/FilterData.script and try again.

eythian · November 24, 2022, 2:37pm

Ah, I see I misunderstood what you were doing there. It is an approach that could work.

This comes across as rather condescending, fyi.

eythian · November 24, 2022, 2:41pm

I don’t think the line is the issue, if I delete it I get the same error but now referring to the second line. It suggests to me that it’s still using the internal driver.

Villeroy · November 24, 2022, 2:52pm

Can you run this macro, please? You can copy the string out of the message box.

Sub ShowDatabaseProperties
doc = ThisComponent
ds = doc.DataSource
settings = ds.Settings
s = "Doc: "& doc.URL & chr(10) _
& "Data Source: "& ds.URL  & chr(10) _
& "JavaDriverClassPath: "& settings.JavaDriverClassPath  & chr(10) _
& "JavaDriverClass: "& settings.JavaDriverClass
MsgBox s
End Sub

eythian · November 24, 2022, 3:02pm

This suggests to me it’s doing the right thing, which confuses me even more.

Villeroy · November 24, 2022, 4:14pm

Sorry, I don’t get it. I downloaded my own database and tested it in various directories and on one Windows box.

eythian · November 24, 2022, 4:25pm

Weird. Ah well, I have a couple of options going forward as it is anyway. Thanks for trying!