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?