SUBSTRING_INDEX not recognised in Doctrine 2

Question

I'm working on a Symfony 3 CRM, and I need to be able to create a list of customers which can be filtered by the first letter of the surname. The issue is, I need to split the column data first in order to find the surname - this is because the database is synced with Sage which requires data in a specific format so I cannot change it.

I discovered the

SUBSTRING_INDEX

function and tried it out in my mySQL software and it works perfectly - so I tried to incorporate it into a DQL query like so:

$query = $em->createQuery("SELECT u FROM AppBundle:Customer c WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(c.contact_name, ' ', 3), ' ', -1) LIKE :letter");

$query->setParameter('letter','%'.$letter.'%');

Where

$letter

is passed via a request from the CRM. However, I get the following error from Symfony when attempting to run:

[Syntax Error] line 0, col 41: Error: Expected known function, got
'SUBSTRING_INDEX'

My guess is that Doctrine does not recognise the SUBSTRING_INDEX function? Is there an alternative way of doing what I need to, or a way to allow Doctrine to recognise this function?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

Doctrine doesn't recognize SUBSTRING_INDEX as a function.

You can import a Doctrine Extension to recognize it or change your query for a thing lke this sql LOWER(SUBSTRING(c.contact_name, LOCATE(' ', c.contact_name)-1))

SUBSTRING_INDEX not recognised in Doctrine 2

Sign up