We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

Parser - COLLATE

In https://forum.phalcon.io/discussion/1810/orm-collate-postgresql- you describe how to use collate with PG however I would like to ask you if there is anyone who can add COLLATE expression to version 1.3.5 and 2.x?

With MSSQL adapter and dialect is possible use COLLATE in WHERE condition only when you write whole SQL manually but it brings more issues with function call in columns. With ORM I get following error: unexpected token IDENTIFIER(COLLATE), What I found problem is in internal parser where you dont count with COLLATE in WHERE condition. Reason why I need COLLATE before LIKE expression is simple - people find records in DB with or without accents so I wanted simply replace:

$_conditions[] = $queryField . " LIKE '%" . $_filter->value ."%'";

with

$_conditions[] = $queryField . " COLLATE Latin1_general_CI_AI LIKE '%" . $_filter->value . "%'";

Does anybody can help me with this problem? Its long time ago I made anything with lexical analyzator.



34.6k
Accepted
answer

You can use the new custom functions feature to extend PHQL to support this feature: https://blog.phalcon.io/post/phalcon-2-0-3-released



10.5k

Did you change internal query builder for functions like "find", "findFirst" etc? I dont have problem with dialect self. Because you formally does not support MSSQLI made custom dialect. However when I use find function I get error before dialect select function is called. Custom dialog function is great when there are internal changes. I'll take a look and let you know if I find any issues. How much is current version stable for production usage? Application / API is deployed in Azure and run as service on IIS.



34.6k

You can create a custom function that allows to set a COLLATE/LIKE as explained in the article. Phalcon 2.0.3 does not have major stability issues that I know



10.5k

Excellent feature :) Need more testing in whole API however for this you have big plus :) Keep it up.