many to many relations using 3rd join table not allowing expansion in identify tool

palmerj commented 2 years ago

What is the bug or the crash?

The manual seems to show it's possible with https://docs.qgis.org/3.22/en/docs/user_manual/working_with_vector/attribute_table.html#introducing-many-to-many-n-m-relations.

However, when using the identify tool, I can't get the table attributes for the 3rd table to show using the join table. See

In the example above I should be able to expand the relationship using field on ttl_title_no field, but it's not an option.

Not sure if it's related but I see the following in message log (repeated each time I click on a feature):

2022-05-27T16:59:05     WARNING    Layer title_parcel_association, field par_id: Missing Relation in configuration
2022-05-27T17:00:47     WARNING    Layer title_parcel_association, field ttl_title_no: Missing Relation in configuration

The relationships seem to be formed ok.

Steps to reproduce the issue

Create new project
Add 1 layer and two join tables. Schema for my example is here.
Create joined as shown in the screenshot above.
Select layer feature with identify tool.
See you can only expand on the first join (fkey_parcel relation using the parcel.id), but not to the second join (fkey_title relation via the title_parcel_association.ttl_title_no field)

Versions

QGIS version	3.22.2-Białowieża	QGIS code revision	1601ec46d0
Qt version	5.15.2
Python version	3.9.5
GDAL/OGR version	3.3.2
PROJ version	8.1.1
EPSG Registry database version	v10.028 (2021-07-07)
GEOS version	3.9.1-CAPI-1.14.2
SQLite version	3.35.2
PDAL version	2.3.0
PostgreSQL client version	12.3
SpatiaLite version	5.0.1
QWT version	6.1.6
QScintilla2 version	2.11.5
OS version	macOS 12.2

Supported QGIS version

[X] I'm running a supported QGIS version according to the roadmap.

New profile

[X] I tried with a new QGIS profile

Additional context

No response

phidrho commented 1 year ago

Hi @palmerj,

can you try with newer version of QGIS? Only thing weird I noticed is that you added duplicated indexes with different names at the end of your SQL.

CREATE INDEX fki_fkey_parcel      ON title_parcel_association USING btree (par_id);
CREATE INDEX fki_fkey_title       ON title_parcel_association USING btree (ttl_title_no);
CREATE INDEX idx_tpa_par_id       ON title_parcel_association USING btree (par_id);
CREATE INDEX idx_tpa_ttl_title_no ON title_parcel_association USING btree (ttl_title_no);

palmerj commented 1 year ago

Hi @phidrho

can you try with newer version of QGIS?

I've updated to 3.28.0

Only thing weird I noticed is that you added duplicated indexes with different names at the end of your SQL.

AH good find. I've removed those.

However, still the same result.

phidrho commented 1 year ago

@palmerj

Can you also please check the new GDAL version? There's been an upgrade to 3.6.0 recently which maybe still isn't available on Mac, but will sure be in next days/weeks.

I didn't think that removing duplicated indexes would fix this behavior, because it's probably not related. Can you send some dump of data? Few parcels and few related titles would be enough, so I'll try to replicate it on Windows. If your data is sensitive please make up some fake data that fits your schema so that I and developers can test it properly on our side.

EDIT: Missing Relation in configuration - I think that this message also isn't related to problem, as I also get this message when using M-to-M relation in GPKG, but everything works as expected.

palmerj commented 1 year ago

Ok great. I will prepare the data tomorrow.

Note the QGIS app bundle version of GDAL is 3.3. See:

QGIS version	3.28.0-Firenze	QGIS code revision	ed3ad0430f
Qt version	5.15.2
Python version	3.9.5
GDAL/OGR version	3.3.2
PROJ version	8.1.1
EPSG Registry database version	v10.028 (2021-07-07)
GEOS version	3.9.1-CAPI-1.14.2
SQLite version	3.35.2
PDAL version	2.3.0
PostgreSQL client version	unknown
SpatiaLite version	5.0.1
QWT version	6.1.6
QScintilla2 version	2.11.5
OS version	macOS 12.6

nyalldawson commented 1 year ago

I can confirm this isn't yet fixed -- the code has not changed at all in recent versions

palmerj commented 1 year ago

Thanks @nyalldawson.

@phidrho do you still want the data to support development work?

phidrho commented 1 year ago

@palmerj I am not currently a developer, but it's always good to give an example data to developers in bug reports so that they can test it and recreate behavior on their machine, which leads to much faster determination of bug in code.

palmerj commented 1 year ago

Small PostGIS test dataset here linz_test_data.sql.zip. Licence terms in the header of the SQL file.

Note while it would be good to get the 3rd join, we actually have relations to plain tables as many as 8 joins. This is due to highly normalised structure of the database.

phidrho commented 1 year ago

Hi @palmerj,

I finally got some time to check your data, and everything seems OK with data, but everything also seems to work as documented.

Unfortunately, I didn't manage to find where in manual is noted that you can recursively loop through nested features:

The manual seems to show it's possible with https://docs.qgis.org/3.22/en/docs/user_manual/working_with_vector/attribute_table.html#introducing-many-to-many-n-m-relations.

You can set up each layer's attribute form to show you data from mapped M-to-M feature, but not with Identify Results Panel.

So, this issue seems to be a feature request and not a bug report.

In the meantime, you can use Actions for relations plugin which can fulfill your needs, but first you need to add some more relations manually which connect a mapping table to data tables - I called them fkey_map_parcel and fkey_map_title - see picture:

additional relations

I recorded you an example how to use plugin.

palmerj commented 1 year ago

Ok, maybe it's not clearly documented that the feature info supports n-m-relations, so let's call it a feature!

phidrho commented 1 year ago

Ok, maybe it's not clearly documented that the feature info supports n-m-relations, so let's call it a feature!

I agree, it's kind of same from user perspective view, but different from QGIS organization view.

Don't get me wrong, I really like the idea, and maybe your institution/organization has budget to invest into this feature.

Here is a list of companies that offer commercial support for implementation of new features.

qgis / QGIS