QGIS and geopackage spatial views

Question

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

workflow:

• 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

    NULL

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?

Answer 1

I retested the geopackage I created using QGIS 2.18.10. I didn't make any changes to the geopackage and the spatial views worked correctly when viewed with 2.18.10.