Doctrine 1
  1. Doctrine 1
  2. DC-800

PostgreSQL does not have LOCATE expressions

    Details

    • Type: Bug Bug
    • Status: 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
        Jonathan H. Wage added a comment -
        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
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: