Hi,
we would like to use Phalcon (1.2.6) ORM, but we have mulilanguage application and we don't know how to set collation for postgresql (9.3). Posgresql doesn't suppport set collation in connection string. Last option we see is generating queries like:
SELECT a FROM b ORDER BY a COLLATE (de_DE)
We guess it is not possible right now.. So what can we do? Any ideas / tips / hacks? Can EventManager help us somehow? We are quite desperate..
Thank you all..
You can use a raw query there: https://docs.phalcon.io/en/latest/reference/phql.html#using-raw-sql
Does it mean everyone using PostgreSQL and having multilanguage app has to give up Phalcon ORM? We would need to write raw select queries for every situation where collation in ordering matters (hundreds of situations).
Can be PHQL syntax extended somehow in future? As I see it
SELECT a FROM b ORDER BY a COLLATE (de_DE)is pretty much valid SQL for every RDBMS, isn't it? For MySQL, you can set it as option for PDO. So no explicit need for this. Maybe that's why nobody noticed yet. But PgSQL is different, so only direct SQL can make it work.
Do we really need to give up things like:
$robot = Robots::findFirst(2);
$robotsParts = $robot->getRobotsParts();Because parts will be unorderable (collation can't be specified).
Phalcon ORM is great thing. We love it. Please help us beat / improve this.
To support this it must be added to the PHQL parser and be translated to each supported database system, this cannot be done from one day to another.
This i completely understand. Just want to agree with you if it can be done and get a talk if there is a chance to have it done in the future.
OR find some trick here, how to set up a PgSQL collation. Do you think it is possible to catch a query before execution (via EventManager), manually change it (append a collation) and then execute this changed query? ORM can still be perfectly working. Just as a hack for everybody encountering this now.
This could work.
You can write those queries using a custom syntax:
SELECT a FROM b ORDER BY COLLATE(a, "de_DE")Then you can create a custom SQL dialect extending the PostgreSQL dialect which intercepts calls to functions returning the right SQL:
<?php
class CustomSQLDialect extends \Phalcon\Db\Dialect\PostgreSQL
{
/**
* Transforms an intermediate representation for a expression into a database system valid expression
*
* @param array expression
* @param string escapeChar
* @return string
*/
public function getSqlExpression($expression, $escapeChar=null)
{
if ($expression["type"] == 'functionCall') {
if ($expression["name"] == 'COLLATE') {
return $expression["arguments"][0] . ' COLLATE ' . $expression["arguments"][1];
}
}
return parent::getSqlExpression($expression, $escapeChar);
}
}The custom dialect must be registered in the connection:
$connection = new \Phalcon\Db\Adapter\Pdo\Postgresql(array(
"host" => "localhost",
"username" => "postgres",
"password" => "secret1",
"dbname" => "template"
"dialectClass" => "CustomSQLDialect"
));We needed to do some things differently, but it is a life saver!
Just to let others know, "dialectClass" is invalid connection option (i got exception), we had to call
$connection->setDialect(new CustomSQLDialect());The actual implementation of COLLATE in getSqlExpression() is much more complex, if you want to do it good and have some validation etc., but it can be done without any problems in a way you showed us. Thanks.