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

[qgib]

Author Name: pinux - (pinux -)
Original Redmine Issue: 2911

Redmine category:data_provider
Assignee: Jürgen Fischer

---

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.

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

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.

---

• resolution was configured as wontfix
• status_id was changed from Open to Closed

[qgib]

Author Name: pinux - (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.

---

• resolution was changed from wontfix to
• status_id was changed from Closed to Feedback

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

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.

---

• status_id was changed from Feedback to Open

[qgib]

Author Name: pinux - (pinux -)

---

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

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

• resolution was changed from to wontfix
• status_id was changed from Open to Closed