Enable the query builder for view that don't have unique id

[pinux]

I have 2 tables: CREATE TABLE polygon (

gid int UNIQUE, name varchar(100) NOT NULL, .... PRIMARY KEY (gid)

) ;

And,

CREATE TABLE line(

gid int UNIQUE, name varchar(100) NOT NULL, .... PRIMARY KEY (gid)

) ;

And a View that makes buffers for the table line and groups the elements by their names:

CREATE OR REPLACE VIEW line_buffer AS SELECT DISTINCT min(l.gid) as gid, l.name, st_union(st_buffer(l.the_geom, 100)) AS the_geom FROM line l GROUP BY l.name ORDER BY l.name;

When I create a View to select the elements from the table polygon that are intersected by the elements in line_buffer, like this:

CREATE OR REPLACE VIEW intersection AS

SELECT DISTINCT p.*

FROM

polygon p, line l

WHERE st_intersects(p.the_geom, ST_merge(l.the_geom)) = TRUE;

When I insert more then 1 element with different names in the table line the result is a View without a column with unique value, because more than one element in line intersects the elements in polygon. If I try to add a query (Build query) in the Add Postgis tables window, qgis tell me that the view don't have a valid column with unique value. But if i write the query ("name"='test1') directly under Sql, I can open the view in qgis. So I suggest that if a view don't have a valid column with unique value it should be possible to open the Build query.

[jef]

In QGIS each feature needs to have a unique id (by which the feature can be retrieved, deleted updated etc.). Filtering by 'test1' probably gives you a unique value and therefore makes the view insertable.

As workaround you could probably combine the gids of polygon and line to produce a unique key.

[pinux]

I have already found a workaround. But, if I can add the Sql statement under the Sql column in the "Add to Postgis table(s)" and open it in qgis I don't see why it should not be possible to open the Query builder and do it in a much easier way. Or if it should not be possible to add a query on a view that don't have unique id (like you suggest), it should not be possible to add the query directly under the Sql column, like it is possible now. If you think that it don't make sense, close again.

[jef]

Replying to pinux:

I have already found a workaround. But, if I can add the Sql statement under the Sql column in the "Add to Postgis table(s)" and open it in qgis I don't see why it should not be possible to open the Query builder and do it in a much easier way.

The query builder needs an layer and alters it's filter/subset string. So the insertion dialog creates the layer and passes it to the query builder - creating the layer only works with a unique key. Entering the where clause directly on the other hand doesn't create an layer and isn't validated immediately, the subset string is just passed on to the layer on creation.

So both option make sense, although it isn't really obvious.

Or if it should not be possible to add a query on a view that don't have unique id (like you suggest), it should not be possible to add the query directly under the Sql column, like it is possible now. If you think that it don't make sense, close again.

[pinux]

Ok, it makes sense. For me you can close the bug.