Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

QueryBuilder: custom SQL function in orderBy clause

I'm trying to fuzzy match location names on the fly with jaro winkler algo.

// custom function registration
class Dialect extends Mysql
{
    public function getSqlExpression(array $expression, $escapeChar = null, $bindCounts = NULL)
    {
        if ($expression["type"] == 'functionCall') {
            switch ($expression["name"]) {
                case 'JARO_WINKLER_SIMILARITY':
                    if (count($expression["arguments"]) != 2) {
                        throw new \Exception('JARO_WINKLER_SIMILARITY requires 2 parameters');
                    }
                    return 'JARO_WINKLER_SIMILARITY('.$this->getSqlExpression($expression["arguments"][0]).', '.$this->getSqlExpression($expression["arguments"][1]).')';
            }
        }
    }
}
// calling the function in orderBy
$country = Country::findFirst(['order'=>['JARO_WINKLER_SIMILARITY(native_name, :name:) DESC'], 'bind'=>['name'=>$providerCountry->getName()]]);

The error i get:

Scanning error before 'JARO_WINKLER_SIM...' when parsing: SELECT [Travelhood\Model\Country].* FROM [Travelhood\Model\Country] ORDER BY [JARO_WINKLER_SIMILARITY(native_name,] :name:) LIMIT :APL0:

The syntax is cleary messed up, it treats the inner argument separator as an orderBy separator

Does PHQL parse custom functions in orderBy at all? Or am i missing something?

oh dude if you can not handle this, the rest of us are screwed... ;)

oh dude if you can not handle this, the rest of us are screwed... ;)

there's always someone with a different angle ;]