Doctrine : how to use Replace function

Question

I need your help to build my query with Doctrine. I am a symfony beginner. Firstly I built my query inside MySQL SQL tab and it's working fine.

SELECT * 
FROM contact
WHERE insee like '03%'
ORDER BY (LENGTH(tif) - LENGTH(REPLACE(tif,";",""))) DESC

To be more precise, my tif field looks like that : 
1 - 01.02.01.02;01.02.03.04;01.05.06 (3 subsets)
2 - 01.02.03.08.07.01.02.03.08.0701.02.03.08.07; (1 subset)
3 - 01.02.01;02.06.05 (2 subsets) 

I need to get the number of codes order by desc so as to get the order 1,3,2.

Now I tried to build it in my repository class on Symfony I found out that replace function doesn't exist upon Doctrine so I tried to skirt it by doing what follows :

$qb = $this->getEntityManager()
            ->createQueryBuilder()
            ->select('c')
            ->from('SgaContactBundle:Contact', 'c')
            ->where('c.insee LIKE :insee')
            ->setParameter('insee', '%' . $insee . '%');

$qb->orderBy($qb->expr()->diff(
            $qb->expr()->length('c.tif'), 
            $qb->expr()->length(preg_match_all('/;/i', 'c.tif')) ),
            'DESC');
return $qb->getQuery()
          ->getResult();

Finally I've got this error :

 [Syntax Error] line 0, col 99: Error: Expected StateFieldPathExpression | string | 
 InputParameter | FunctionsReturningStrings | AggregateExpression, got '0'

What can I do in order to replace "Replace function" ? I tried preg_replace, preg_match and finally preg_match_all but something goes wrong.

Thanks for your help guys

Solution

You can implement your own function like the validate response, OR you can use this Doctrine Extension package that include REPLACE() mysql function and much more (Match against, ROUND, ASIN...) : https://github.com/beberlei/DoctrineExtensions

Simply install this to your project

composer require beberlei/doctrineextensions

And add the function you want (only) into your config.yml doctrine conf :

# Doctrine Configuration
doctrine:
    orm:
    entity_managers:
        default:
        #...
            dql:
                string_functions:
                    match: DoctrineExtensions\Query\Mysql\MatchAgainst
                    replace: DoctrineExtensions\Query\Mysql\Replace

Here is a small exemple for REPLACE in query builder :

$qb = $this->createQueryBuilder('ts')
    ->where("REPLACE(ts.reference, ' ','') LIKE :reference")
    ->setParameter('reference', $reference)
;

Hope it can help someone after