Bug report #10913

n:n relations

Added by Denis Rouzaud over 5 years ago. Updated almost 4 years ago.

Status:Closed
Priority:Normal
Assignee:Matthias Kuhn
Category:Relations
Affected QGIS version:2.4.0 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:fixed/implemented
Crashes QGIS or corrupts data:No Copied to github as #:19276

Description

n:n relations can somehow be handled in QGIS, but the UI (forms) is not very usable in its current form.
See attached image.

I will take the example of a pipe layer and a maintenance table (and a join table).
It would be nice to remove the form of the joining table and only display the maintenance form.

Is this possible?

Screenshot.png (37.7 KB) Denis Rouzaud, 2014-07-18 06:59 AM

project.qgs (16.7 KB) Denis Rouzaud, 2014-07-18 07:00 AM

nn_rel.sqlite (4.21 MB) Denis Rouzaud, 2014-07-18 07:00 AM

History

#1 Updated by Denis Rouzaud over 5 years ago

Here is a demo project with a spatialite db.

#2 Updated by Denis Rouzaud over 5 years ago

#3 Updated by Matthias Kuhn over 5 years ago

Basically what you want is to hide the FK back to the parent form (id: 3) and the label id_pipe if I understand correctly.

A few other things to consider are:

  • In the list view on the left, it would be good to show not only the FK but a meaningful description based on data from the embedded form's layer. This would require expression support for relations.
    • This operation can be potentially slow if the expression is evaluated (and data retrieved from the DB) once per record. The following ideas could help in this case:
      • Support for an expression compiler including JOIN
      • Caching of the embedded table (but that can potentially be very memory intensive)
  • Edit mode: To add a new related feature you will need to switch both layers (embedded and linking table) into edit mode. It would be nice to do this with one click.
    • Should edit modes of different layers be "linked". E.g. when the linking table is switched to edit mode, the referenced layers are automatically switched to edit mode as well?
    • A project wide edit button instead of per layer edit buttons
  • The buttons (link/unlink/add/remove) will need to be reconsidered. The first two would need to update the linking table, the second two intermediate and embedded layer.
  • What is the table view supposed to show?
    • Probably the records from the embedded layer.

A mockup of a form perfectly designed to suit the needs of a n:m relation would also be a very good start.

#4 Updated by Denis Rouzaud over 5 years ago

I think we should go for a general support of nn relations.
This would be started from the joining table.
As soon as there is a join table, QGIS would adapt relations to go through this table.

Regarding displaying meaningful data in the list, this could be handled using standard joins in the joining table. Hence, you would have access to related lines, in both direction.

Yes, editing should be nicely handled. I would say editing would be handled from the relations itself. The idea would be to create a specific type for nn-relations: it would handle the nesting of forms through the join table and would also take care of turning on/off editing for the layers.

Not sure we have to show anything else than the layer itself in the attribute table.

#5 Updated by Matthias Kuhn over 5 years ago

As soon as there is a join table, QGIS would adapt relations to go through this table.

What does "go through" mean exactly?

Regarding displaying meaningful data in the list, this could be handled using standard joins in the joining table. Hence, you would have access to related lines, in both direction.

Good point. That let's us separate the two things.

Yes, editing should be nicely handled. I would say editing would be handled from the relations itself. The idea would be to create a specific type for nn-relations: it would handle the nesting of forms through the join table and would also take care of turning on/off editing for the layers.

That means that the pencil on the embedded form toggles the editing modes of both layers but the pencil on the main window works on a single layer?
Or does that mean, that the editing modes of the two related layers are synchronized with the edit mode of the joining layer?

Not sure we have to show anything else than the layer itself in the attribute table.

The joining table might contain additional information fields as well. Would it be more appropriate to show rows merged together from the joining and the related table, just hiding the foreign keys on the joining table.

#6 Updated by Denis Rouzaud over 5 years ago

By "go through", I meant that QGIS would be able to reach the maintenance table directly through the join table. In other words, QGIS handles nn relations, but the join table is not directly visible to the user.

If two relations are defined on the same layer (the joining table), then there could be an option to couple it with another.
Then, QGIS handles the nn:relations and the joining table is not shown anymore.

Then, indeed the pencil in the main form (pipes) still works as now, but the pencil in the embedded form makes the joining table + the maintenance table editable.

Regarding the attribute table, this would also be handled by the layer joins (not relations).
Martin will fix the code so joining fields can be nested. So I believe, this will be automatically handled.

#7 Updated by Matthias Kuhn almost 4 years ago

  • Resolution set to fixed/implemented
  • Status changed from Open to Closed

Implemented in 2.14

Also available in: Atom PDF