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.
paddocks.geom
?) or about the SQL query?