Using group by to set a PostGIS datasource using QGIS Python API

Question

I am writing a QGIS Plugin and wish to obtain data from a PostGIS table using the Group By clause using something like the following code:

uri = QgsDataSourceURI()
uri.setConnection('localhost', '5432', 'postgis', 'postgres', 'xxx')
Query="select param_id, st_union(the_geom) as the_geom from myTable group by param_id"
uri.setDataSource('', '(%s)' % postGisQuery, "the_geom",'','gid')
vlayer1 = QgsVectorLayer(uri.uri(), 'testlayer1', "postgres") 

This gives the error "No key field for query given." because it is looking for the gid key field. But if gid was put in the select statement of the Query, we won't get aggregated rows, just distinct rows. If I try an aggregation function on gid, I get the same error. If I try aliasing the aggregate, I get an "Erroneous query" error (even count(gid) as gid), same as if I put 'param_id' as the key field in setDataSource, because 'param_id' is not a primary key, I guess.

In this case, forcing the key field in the uri seems to make the group by clause useless. Is there another workaround I haven't thought of or is this the expected behaviour?

EDIT: in QGIS version 1.7.3 and my plugin code like below works, but not in my dev version (1.9.90):

Query="select param_id, st_union(the_geom) as the_geom from myTable group by param_id"
uri.setDataSource('', '(%s)' % postGisQuery, "the_geom",'','param_id')
vlayer1 = QgsVectorLayer(uri.uri(), 'testlayer1', "postgres") 

So it allowed the use of param_id as a key, even though it isn't a primary key. Has something changed in recent versions that doesn't allow such code? In fact there doesn't seem to be an error message in the Log Messages, QgsVectorLayer just doesn't load the layer - using QgsMapLayerRegistry.instance().addMapLayer(vlayer1)

Does anyone know if such code should work in the latest dev version (maybe there is a more recent version that allows this or the feature has been deliberately discontinued?)

Ian

Answer 1

From my own further investigation it looks like such a group by clause is not useful because the primary key needs to be included as a field and so no grouping will actually be done.

As commented on another website where I asked this question, the data will need to be preprocessed and probably best made available as a view.

To do this dynamically (as I was initially trying to do), the answer would be to create a table (or view) on the fly in PostGIS and then set the datasource to show this data in QGIS. I don't like creating a lot of tables just for the sake of viewing data, but can't see another way of doing it.