Feature request #3858
Not possible to define subset of layer using field from joined table
|Pull Request or Patch supplied:||No||Resolution:|
If a table is dynamically joined to a vector layer (as possible from QGIS 1.7), it is not possible to define a subset of the layer to be displayed (as done in Layer Properties - General) based on a field of the joined table.
If an SQL-Where-Clause is entered that refers to a field of the joined table (in this case "MAINCROP", when clicking OK, an error message appears:
"OGRr3 error 1: SQL: Failed to identify field:MAINCROP"
It would be good if this were made possible, as joining tables dynamically makes less sense if for some purposes, the joined datasets still have to be permanently saved into a new dataset.
#1 Updated by Even Rouault over 6 years ago
I believe you've just hit a limitation of the OGR SQL engine.
http://gdal.org/ogr/ogr_sql.html mentions :
WHERE Limitations 1. Fields must all come from the primary table (the one listed in the FROM clause).
There's however a workaround (at least from a programmer point of view...) : instead of issuing the ExecuteSQL() with the where clause included, just issue the ExecuteSQL() with only the join and on the resulting layer, apply a SetAttributeFilter() with the where clause.
Illustration with ogrinfo (>= 1.8.0 --> from the error message you get I see you are using GDAL/OGR < 1.8) :
ogrinfo ../autotest/ogr/data -al -sql "SELECT * FROM poly LEFT JOIN idlink ON poly.eas_id = idlink.eas_id WHERE idlink.name = '_170_'"
doesn't work (results in a layer with no features),
ogrinfo ../autotest/ogr/data -al -sql "SELECT * FROM poly LEFT JOIN idlink ON poly.eas_id = idlink.eas_id" -where "idlink.name = '_170_'"
The dark side of this is that applying the where clause as an attribute filter after getting the OGR layer could be less efficient than applying it in the SQL request, in the case the where clause would only refer to fields of the primary table and that an index existed on those fields
#2 Updated by Marco Hugentobler over 6 years ago
The subset string is meant to work on provider level (e.g. on the database to have best speed). It would be a possibility to have a fallback to clientside search string. For the moment, the dialog is just greyed out as soon as there are joins on the layer (commit 3ac604ff1be4676e69e2).
#6 Updated by Médéric RIBREUX about 2 years ago
- Operating System changed from Linux to All
- OS version deleted (
- Pull Request or Patch supplied set to No
Hello, bug triage...
in QGIS 2.13 you can't filter a layer with the joined attributes (on the Query Builder).
But you can make selections with expressions on the joined attributes.