0

i am having an error while using custom mysql function in doctrine query builders query string.
[Syntax Error] line 0, col 32: Error: Expected known function, got 'ucfirst'
mysql function is as below. 

DELIMITER $$
DROP FUNCTION IF EXISTS `ucfirst`$$
CREATE FUNCTION `ucfirst`(str_value VARCHAR(5000)) RETURNS varchar(5000) CHARSET latin1 DETERMINISTIC
BEGIN
RETURN CONCAT(UCASE(LEFT(str_value, 1)),SUBSTRING(str_value, 2));  
END$$
DELIMITER;

Doctrine query code is as below. 

$qb = $this->em->createQueryBuilder();
$qb->select("ConcatWs(' ',ucfirst(p.firstName), ucfirst(p.lastName)) as user_name");
$qb->from('Entity\Profile', 'p');
$data = $qb->getQuery()->getResult();
print_r($data);exit;

any suggestions where i am doing wrong ?

Amit Joshi
  • 456
  • 1
  • 8
  • 21

1 Answers1

0

Doctrine stays on top of DBAL - abstraction layer for database access, its purpose is to hide details of implementation of particular database. DQL, query language defined by Doctrine, is also built in a way to remain generic enough. It means that Doctrine is designed in a way to provide unified interface and hence provides no built-in facilities for use some database-specific extensions.

However Doctrine is flexible enough to let you to extend DQL AST and implement required database-specific extensions by yourself. In particular custom DQL function (that needs to be mapped to actual SQL) can be created by implementing own FunctionNode and register it as additional functional node into EntityManager configuration. In your case it will be $em->getConfiguration()->addCustomStringFunction().

Please refer Doctrine documentation for example of custom function implementation.

Flying
  • 4,422
  • 2
  • 17
  • 25
  • hello, i have created custom doctrine extensions but dont know how to create custom mysql function. can you elaborate it with example ? – Amit Joshi Jan 10 '19 at 09:43
  • @AmitJoshi this is hard part of Doctrine that requires knowledge of internal representation of DQL AST and related areas. I have no working example and there is little documentation on this topic, you can try to check [this](https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/cookbook/dql-user-defined-functions.html#dql-user-defined-functions) part of documentation for examples. – Flying Jan 10 '19 at 10:50