Doctrine 1

PostgreSQL does not have LOCATE expressions

Details

  • Type: Bug Bug
  • Status: Resolved Resolved
  • Priority: Critical Critical
  • Resolution: Fixed
  • Affects Version/s: None
  • Fix Version/s: 1.2.3
  • Component/s: Native SQL, Query
  • Labels:
    None
  • Environment:

Description

Introduction

As it's described in Doctrine documentation: http://www.doctrine-project.org/projects/orm/1.2/docs/manual/dql-doctrine-query-language/en#functional-expressions. In case I have correctly understood this documentation, I can use registered expressions (CONCAT,TRIM,LOCATE etc.) with supported database drivers.

Issue

The problem is with PostgreSQL, - it does not have the string function "LOCATE" since v7.4 (i have no info about previous version)

Here is my example:

  $q = PortalTable::getInstance()
      ->createQuery()
      ->addSelect(
        '(1 <= LOCATE(host, ?) as is_host_matched)',
        array('.google.com')
  );

  $q->execute();

And this code dies with an error:

Doctrine_Connection_Pgsql_Exception: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "as" LINE 1: SELECT (1 <= LOCATE("p"."host", $1) as is_host_matched) AS "... ^. Failing Query: "SELECT (1 <= LOCATE("p"."host", ?) as is_host_matched) AS "p__0" FROM "portal" "p"" in /web/vendor/symfony/1.4-svn/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Connection.php on line 1082

In PgSQL you can use POSITION for this needs (I want to mention, MySQL has this function too as an alias for LOCATE)

btw, POSITION is SQL-92 standard http://owen.sj.ca.us/~rk/howto/sql92.html - maybe it is better to rename LOCATE with POSITION?

Patch

Index: Doctrine/Expression/Pgsql.php
===================================================================
--- Doctrine/Expression/Pgsql.php	(revision 7678)
+++ Doctrine/Expression/Pgsql.php	(working copy)
@@ -230,4 +230,31 @@
     	$translate = 'TRANSLATE(' . $string . ', ' . $from . ', ' . $to . ')';
     	return $translate;
     }
-}
\ No newline at end of file
+
+    /**
+     * transform locate to position
+     *
+     * @param string $substr string to find
+     * @param string $str to find where
+     * @return string
+     */
+    public function locate($substr, $str)
+    {
+        return $this->position($substr, $str);
+    }
+
+    /**
+     * position
+     *
+     * @param string $substr string to find
+     * @param string $str to find where
+     * @return string
+     */
+    public function position($substr, $str)
+    {
+        $substr = $this->getIdentifier($substr);
+        $str = $this->getIdentifier($str);
+        
+        return sprintf('POSITION(%s IN %s)', $substr, $str);
+    }
+}

Solution without patch:

  # will work with PgSQL and MySQL (tested)
  $exp = new Doctrine_Expression('POSITION(host IN ?)');

  $q = PortalTable::getInstance()
      ->createQuery()
      ->addSelect(
        "(1 <= {$exp} as is_host_matched)",
        array('.google.com')
  );

  $q->execute();

Activity

Hide
Permalink
Jonathan H. Wage added a comment -

Fixed in http://trac.doctrine-project.org/changeset/7685

Thanks, Jon

Show
Jonathan H. Wage added a comment - Fixed in http://trac.doctrine-project.org/changeset/7685 Thanks, Jon

People

  • Assignee:
    Jonathan H. Wage
    Reporter:
    Ilya Sabelnikov
Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved: