[mapserver-users] IN operator via queryByAttribute no longer works in php-mapscript Mapserver 7.0.2

[Moen, Paul T.]

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



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20161020/7c49fa7e/attachment-0001.html>