Feature request #3858

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

Added by bnotter - almost 6 years ago. Updated 23 days ago.

Status:Open
Priority:Low
Assignee:-
Category:Vectors
Pull Request or Patch supplied:No Resolution:
Easy fix?:No

Description

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.

History

#1 Updated by Even Rouault almost 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),

but :

ogrinfo ../autotest/ogr/data -al -sql "SELECT * FROM poly LEFT JOIN idlink ON poly.eas_id = idlink.eas_id"  -where "idlink.name = '_170_'" 

does

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

#3 Updated by Giovanni Manghi over 5 years ago

  • Target version changed from Version 1.7.0 to Version 1.7.4

#4 Updated by Giovanni Manghi about 5 years ago

  • Target version changed from Version 1.7.4 to Version 2.0.0

#5 Updated by Pirmin Kalberer over 4 years ago

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

#6 Updated by Médéric RIBREUX over 1 year 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 23 days ago

  • Easy fix? set to No

Also available in: Atom PDF