chris hermansen <clherman...@gmail.com> ezt írta (időpont: 2025. ápr. 24., Cs, 20:07):
> 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. > Sorry, I really worded it confusingly, the table names are in Hungarian, I overlooked something, let me clarify The main table is the findings, in which the "type" column is a foreign key to the "uuid" column of the types table, the material column is a foreign key to the "uuid" column of the material table 1. table - findings 2. table - types 3. table - material 4. table - findings material (connecting table), N:M relationship The sql query: SELECT a.uuid AS material_uuid, a.values AS material_value FROM material AS a JOIN findings_material AS pivot ON a.uuid = pivot.material_fk WHERE pivot.types_fk = '6c5356d0-3257-49ed-8f95-4262d33175c9'; All this is being done for a qfield project,. I would like to solve the result of this sql query during data collection, that is, I create a point with gps, select the type of the find, and in the material column, only those values are displayed that are assigned to this type in the connection table.
_______________________________________________ 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
