Feature request #3858

Not possible to define subset of layer using field from joined table

Added by bnotter - over 12 years ago. Updated almost 7 years ago.

Pull Request or Patch supplied:No Resolution:
Easy fix?:No Copied to github as #:13916


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 12 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),

but :

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 12 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).

#3 Updated by Giovanni Manghi about 12 years ago

  • Target version changed from Version 1.7.0 to Version 1.7.4

#4 Updated by Giovanni Manghi almost 12 years ago

  • Target version changed from Version 1.7.4 to Version 2.0.0

#5 Updated by Pirmin Kalberer over 11 years ago

  • Target version changed from Version 2.0.0 to Future Release - Nice to have

#6 Updated by Médéric RIBREUX about 8 years ago

  • Operating System changed from Linux to All
  • OS version deleted (Ubuntu 10.10)
  • 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.

#7 Updated by Giovanni Manghi almost 7 years ago

  • Easy fix? set to No

Also available in: Atom PDF