I cannot get the IN operator to work with queryByAttributes in the new version
of php_mapscript. I see nothing obvious mentioned in the migration guide and
have found no examples in the documentation on the syntax expected by Mapserver
7.0.2.
The following queryByAttribute using an IN clause worked with MapServer version
6.4.2.
queryByAttributes(site_id,(site_id IN (4243,4468,3142)),MS_MULTIPLE);
The following select was created.
msPostGISLayerWhichShapes query: select
"site_id","county","station_location","longitude","latitude","status",encode(ST_AsBinary(ST_Force2D("the_geom"),'NDR'),'hex')
as geom,"site_id" from (select
p.site_id,p.the_geom,p.county,p.station_location,
p.longitude,p.latitude,p.status from precip.station p) as foo where the_geom &&
ST_GeomFromText('POLYGON((1026879.66418811 54774.2883737648,1026879.66418811
1331032.73787942,2999091.34586756 1331032.73787942,2999091.34586756
54774.2883737648,1026879.66418811 54774.2883737648))',2266) and (site_id IN
(4243,4468,3142))
The same queryByAttribute as above fails with Mapserver version 7.0.2 and
listed below is the select statement that is created.
msPostGISLayerWhichShapes query: select
"site_id","county","station_location","longitude","latitude","status",encode(ST_AsBinary(ST_Force2D("the_geom"),'NDR'),'hex')
as geom,"site_id" from (select
p.site_id,p.the_geom,p.county,p.station_location,
p.longitude,p.latitude,p.status from precip.station p) as foo where the_geom &&
ST_GeomFromText('POLYGON((1026879.66418811 54774.2883737648,1026879.66418811
1331032.73787942,2999091.34586756 1331032.73787942,2999091.34586756
54774.2883737648,1026879.66418811 54774.2883737648))',2266) and
((4243.000000,4468.000000,3142.000000))
The select statement created by 7.0.2 is incorrect with the parameters that I
passed in in the previous version.
Specifically, the final and of the where clause went from 'and (site_id IN
(4243,4468,3142))' to 'and ((4243.000000,4468.000000,3142.000000))'.
I then tried to figure out the correct syntax of a mapserver expressions and
added brackets around the parameter.
queryByAttributes(site_id,([site_id] IN (4243,4468,3142)),MS_MULTIPLE);
This gave the following select statement.
msPostGISLayerWhichShapes query: select
"site_id","county","station_location","longitude","latitude","status",encode(ST_AsBinary(ST_Force2D("the_geom"),'NDR'),'hex')
as geom,"site_id" from (select
p.site_id,p.the_geom,p.county,p.station_location,
p.longitude,p.latitude,p.status from precip.station p) as foo where the_geom &&
ST_GeomFromText('POLYGON((1026879.66418811 54774.2883737648,1026879.66418811
1331032.73787942,2999091.34586756 1331032.73787942,2999091.34586756
54774.2883737648,1026879.66418811 54774.2883737648))',2266) and
("site_id"(4243.000000,4468.000000,3142.000000))
Now the where clause looks like
("site_id"(4243.000000,4468.000000,3142.000000)). This is close but the IN
disappears between site_id and the list of values.
The only way I can get this to work is to use a regular expression. This seems
like a work around since an integer field is being cast as text in order to do
a regular expression.
queryByAttributes(site_id,([site_id] ~* "^(4243|4468|3142)$"),MS_MULTIPLE);
Resulting select statement.
msPostGISLayerWhichShapes query: select
"site_id","county","station_location","longitude","latitude","status",encode(ST_AsBinary(ST_Force2D("the_geom"),'NDR'),'hex')
as geom,"site_id" from (select
p.site_id,p.the_geom,p.county,p.station_location,
p.longitude,p.latitude,p.status from precip.station p) as foo where the_geom &&
ST_GeomFromText('POLYGON((1026879.66418811 54774.2883737648,1026879.66418811
1331032.73787942,2999091.34586756 1331032.73787942,2999091.34586756
54774.2883737648,1026879.66418811 54774.2883737648))',2266) and
("site_id"::text ~* '^(4243|4468)$')
How do I properly format the query string for queryByAttributes to search for
an attribute using a where in clause? Is the regular expression approach the
only way to accomplish the search?
Thanks for the help,
Paul
_______________________________________________
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users
