1

For the first time I tried to set up a one to many relationship in QGIS.24 using Postgis spatial table and a view. The view includes a foreign field key referring to the table's primary key.

 CREATE TABLE paddocks(
 id serial NOT NULL
 name character varying(45) NOT NULL,
 area numeric(5,2) NOT NULL,
 sectionid smallint NOT NULL,
 geom geometry (POLYGON,5347)
 CONSTRAINT paddocks_pkey PRIMARY KEY (id))

 CREATE VIEW es_cattle_existence_by_paddock_category
 SELECT p.name,
 cc.category,
 sum(COALESCE(ci.move_in, 0)) - sum(COALESCE(ci.move_out, 0)) AS quantity,
 ci.paddockid
 FROM cattle_inventory ci
 JOIN cattle_category cc ON ci.categoryid = cc.id
 JOIN paddocks p ON ci.paddockid = p.id
 GROUP BY p.name, cc.category, ci.paddockid
 ORDER BY p.name, cc.category;

Add Relation (QGIS Project Properties)
Referenced (parent): paddocks
Field: id

Referencing (child) : es_cattle_existence_by_paddock_category
Field: paddockid

The relation, set up in Projects Properties relations, works just fine and allows displaying the child fields (view) at the paddock attribute table, but they are not displayed at the Layer Properties - Labels - (form value field) nor at the expression dialog or at the field calculator.

The relation (test_paddock) is shown at the Layers Properties' Attribute Forms section under Relations, but the child fields (view) do no show at the Form Layout when using the Drag and Drop Designer.

I followed the instructions of the QGIS user manual (15.2.6 Creating one or many to many relations). I tried in a different Windows PC using QGIS.22, and using tables with full set foreign keys and constrains always with the same result. My goal would be to use se child's field values at the label of the paddock (parent) layer.

2
  • Plase explain the main problem, is about QGIS visualization (you not see the paddocks.geom?) or about the SQL query? Oct 13, 2022 at 22:17
  • @Peter Krauss it is neither a visualization nor a SQL query issue, but the fact that the one to many relation it is not working, or at least it is not working in my environment. I can not access the child fields and the result of setting the relation differs from the expected as per QGIS manual.
    – Erick
    Oct 14, 2022 at 2:47

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.