I'm working on a project where I want to deal with one-to-many relationships using a geopackage. I can create layers and non-spatial tables but I'm having trouble with the spatial view. I want to have many condition audits conducted on a footpath and want a view to display only the most recent condition audit but QGIS doesn't seem to be working with a spatial view in the geopackage.
Software versions: QGIS 2.18.3, 'DB Browser for SQLite' version 3.9.1
Using QGIS, create a polygon layer using 'Layer>Create Layer>New Geopackage Layer'
Then create a non-spatial layer using the same method and selecting the same geopackage but select the ‘Geometry type’ as 'non spatial'
Using DB Browser enable foreign keys checkbox in pragams and add a foreign key constraint to non-spatial table (FootpathCondition)
Then create a spatial view:
CREATE VIEW FootpathConditionRatings as Select fp2.fid as fid, fpc2.fid as AuditID, fpc2.AuditData as DateAudited, fp2.geometry as geometry from Footpath as fp2 INNER JOIN FootpathCondition as fpc2 ON fpc2.FK_Footpath = fp2.fid where AuditID in( select AID from( Select fp.fid as fid, fpc.fid as AID, MAX(fpc.AuditData) as DateAudited from Footpath as fp INNER JOIN FootpathCondition as fpc ON fpc.FK_Footpath = fp.fid group by fp.fid ) )
add values to gpkg_contents and gpkg_geometry_columns to make the view a spatial layer
using QGIS populate tables adding 2 polygons and 8 records in non-spatial table (representing 4 transactions against each polygon)
Using DB Browser check 2 tables' and the view's data. View returns 2 rows with the most recent date, as expected.
fid AuditID DateAudited "2" "3" "2017-02-14" "1" "8" "2016-05-14"
Add view to QGIS using DB Manager and check data
Outcome: QGIS displays the view's geometry correctly however the 2 records in the attribute table are the same values. If I copy the 2 records to the clipboard and paste then these are the values I get:
wkt_geom fid AuditID DateAudited
If I select a row in the attribute table both rows are selected and both geometries are also selected.
I'm wondering if anyone else is getting this behaviour with spatial views in a geopackage?