I am running:
Drupal 7.17
Database System = Postgresql / 9.1.6

I have created a hook_views_query_alter function that substitutes
node field values into the filter values I set up with views.

This allows me to use an external database connection to create a view using
node fields as the filter.

This works fine for strings, but I would like to use the postgres && operator and
the ST_* functions to add postgis functionality to the view.

Here is the code inside my hook_views_query_alter function:
$query->where[1]['conditions'][3]['field'] = 'geom';
$query->where[1]['conditions'][3]['value'] = 'ST_AsText(ST_Transform(\'' .$current_node->field_map_extent[LANGUAGE_NONE][0]['geometry'] . '\',900913))';
$query->where[1]['conditions'][3]['operator'] = '&&';

The error I'm getting is:
"SQLSTATE[XX000]: Internal error: 7 ERROR: parse error - invalid geometry LINE 6: ... AND (geom && 'ST_AsText...
^ HINT: "ST" <-- parse error at position 2 within geometry"

I believe this is because the ST_... value has quotes around it when the query is executed.

example query from debugger session
========================
SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM parcels_pugetsound parcels_pugetsound WHERE (( (geom && "ST_AsText(ST_Transform('0103000020E6100000010000000500000086670FF461865EC085233789A2FA4740A5C610748C815EC085233789A2FA4740A5C610748C815EC0492BDC3B0CFF474086670FF461865EC0492BDC3B0CFF474086670FF461865EC085233789A2FA4740',900913))") ))) subquery

This query doesn't run on postgres but if I remove the " around "ST_AsText..." then it works fine.

I can't seem to debug any deeper than:
includes/database/database.inc:line 2140
The line "parent::execute($args);" crashes when I attempt to step into it.

Can I strip off quotes from the value field if the operator field is '&&' ??

CommentFileSizeAuthor
#1 d7-add_intersect_operator.patch726 bytesd.butler
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

d.butler’s picture

SOLUTION
I created a workaround for my problem, but I had to kill a kitten.

The attached patch to /includes/database/query.inc just concats (field . operator . value) if the operator is &&. There is no placeholder inserted into $condition_fragments[] and the value is not inserted into $arguments.

This keeps the value (in my case, postgis functions like ST_AsText...) from being quoted in the eventual PDOStatement.

Not sure if there is another way to keep the value from being quoted or if there are other use case besides my postgis && operator that would need the value to be passed on without quotes.

REASON
I needed this in order to add PostGIS awareness to a view that is pulling data from an external database. The filters are coming from node fields including one that stores the current node's openlayers viewport.

Version: 7.17 » 7.x-dev

Core issues are now filed against the dev versions where changes will be made. Document the specific release you are using in your issue comment. More information about choosing a version.