[QGIS-trac] Re: [Quantum GIS] #3858: Not possible to define subset of layer using field from joined table

[Quantum GIS]

#3858: Not possible to define subset of layer using field from joined table
------------------------------------------------+---------------------------
   Reporter:  bnotter                           |              Owner:               
       Type:  bug                               |             Status:  new          
   Priority:  major: does not work as expected  |          Milestone:  Version 1.7.0
  Component:  Vectors                           |            Version:  Trunk        
   Keywords:  table join, subset                |   Platform_version:  Ubuntu 10.10 
   Platform:  Linux                             |           Must_fix:  No           
Status_info:  0                                 |  
------------------------------------------------+---------------------------

Comment(by rouault):

 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

-- 
Ticket URL: <https://trac.osgeo.org/qgis/ticket/3858#comment:1>
Quantum GIS <http://qgis.org>
Quantum GIS is an Open Source GIS supporting GDAL/OGR, PostGIS, and GRASS formats, and Web Services