Bug report #2911
Enable the query builder for view that don't have unique id
| Status: | Closed | ||
|---|---|---|---|
| Priority: | Low | ||
| Assignee: |  Jürgen Fischer | ||
| Category: | Data Provider | ||
| Affected QGIS version: | Regression?: | No | |
| Operating System: | Windows | Easy fix?: | No |
| Pull Request or Patch supplied: | Resolution: | wontfix | |
| Crashes QGIS or corrupts data: | Copied to github as #: | 12971 |
Description
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.
History
#1
Updated by Jürgen Fischer over 14 years ago
- Resolution set to wontfix
- Status changed from Open to Closed
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.
#2
Updated by pinux - over 14 years ago
- Resolution deleted (
wontfix) - Status changed from Closed to Feedback
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.
#3
Updated by Jürgen Fischer over 14 years ago
- Status changed from Feedback to Open
Replying to [comment:2 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.
#4
Updated by pinux - over 14 years ago
Ok, it makes sense. For me you can close the bug.
#5
Updated by Jürgen Fischer over 14 years ago
- Resolution set to wontfix
- Status changed from Open to Closed