Details
-
Type:
Bug
-
Status:
Resolved
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: 1.2.3
-
Component/s: Native SQL, Query
-
Labels:None
-
Environment:HidePHP: v5.2.13 (cli) (built: May 6 2010 01:51:58) Zend Engine v2.2.0, Xdebug v2.0.5
OS: FreeBSD x 8.0-RELEASE-p2 FreeBSD 8.0-RELEASE-p2 #0: Thu May 6 03:37:19 EEST 2010 x@y.z:/usr/obj/usr/src/sys/CUSTOM_8_0 amd64
Database: postgres (PostgreSQL) 8.4.3
Symfony: 1.4.7-DEV (/web/vendor/symfony/1.4-svn/lib)
Web-server: nginx/0.7.65ShowPHP: v5.2.13 (cli) (built: May 6 2010 01:51:58) Zend Engine v2.2.0, Xdebug v2.0.5 OS: FreeBSD x 8.0-RELEASE-p2 FreeBSD 8.0-RELEASE-p2 #0: Thu May 6 03:37:19 EEST 2010 x@y.z :/usr/obj/usr/src/sys/CUSTOM_8_0 amd64 Database: postgres (PostgreSQL) 8.4.3 Symfony: 1.4.7-DEV (/web/vendor/symfony/1.4-svn/lib) Web-server: nginx/0.7.65
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();
Fixed in http://trac.doctrine-project.org/changeset/7685
Thanks, Jon