Kosza and list, On Thu, Apr 24, 2025 at 3:34 AM Kosza Antal via QGIS-User < qgis-user@lists.osgeo.org> wrote:
> Hello everyone, > > I want to implement the following in qgis > > Given 3 geopackage tables, one is the findings, the second is the type, > the third is the material, and the fourth is the findings_material. The > latter is a connecting table between the type and the material. The tables > refer to each other with foreign keys. The foreign key is of type uuid. > > The task: in qgis, the findings table needs to be edited, the findings > field refers to the type uuid with a foreign key, the material field refers > to the uuid key of the material table. qgis automatically recognizes the > relationships and sets up a relation reference widget. > > What I want: when I select the finding, i.e. the corresponding value from > the type table, in the material it should filter the values based on the > pivot table. > This is how it looks in sql. > > SELECT > a.uuid AS material_uuid, > a.values AS material_values > FROM > material AS a > JOIN > result_material AS pivot ON a.uuid = pivot.material_fk > WHERE > pivot.characteristic_fk = '6c5356d0-3257-49ed-8f95-4262d33175c9'; > > Just wondering if you could create a view based on your SELECT statement that is editable (I've not done this in SQLite so I have no idea if it's possible). Looking at your SQL vs your description, I'm not following. You say "the findings table needs to be edited"... Then it seems to me you need a SELECT statement like this: SELECT f.*,t.*,m.* FROM findings AS f JOIN type AS t ON f.type_uuid = t.uuid JOIN material AS m ON f.material_uuid = m.uuid I don't understand the purpose of the table you call "findings_material" in one place and "result_material" in another and which you say is "a connecting table between the type and the material". It seems to me the findings table connects type and material (as per my SELECT statement). Are you using it to restrict the combinations of type and material? If that's the case, then you should probably relate the findings table to that restricting table rather than type and material directly. I would probably choose to call that restricting table something like "allowed_type_material" and have a field on findings that was called "allowed_type_material_uuid". Sorry that I'm not finding your problem description clear; I hope my comments don't add confusion. -- Chris Hermansen · clhermansen "at" gmail "dot" com C'est ma façon de parler.
_______________________________________________ QGIS-User mailing list QGIS-User@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
