Doctrine DQL - Logic check in select

Question

I am trying to use a LIKE comparison in a doctrine DQL in a MySQL database. It works directly in SQL in the database and looks like this:

SELECT *, (name LIKE '%testO%') as partOfName
from organization
ORDER BY partOfName DESC;

This works just fine.

Now I try to implement this logic in Doctrine. My Querybuilder looks like this:

oQueryBuilder
  ->from(OrganizationEntity::class, organization)
  ->select('organization')
  ->addSelect('(organization.name LIKE %:searchTerm%) AS searchTermIsPartOfName')
  ->setParameter('searchTerm', $sSearchTerm)
  ->orderBy('searchTermIsPartOfName', 'DESC')
;

Trying to run it or get the SQL out of it gives me the following error:

[Syntax Error] line 0, col 97: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got 'LIKE'

It is definitely the part about the LIKE. I commented the last three lines out and it works.

How do I translate the above working SQL into Doctrine DQL?

iainn iainn · Accepted Answer · 2020-02-06T11:46:31

LIKE expressions are covered under Conditional Expressions in DQL's grammar. And unfortunately, it's not possible to use these directly within a SelectExpression.

However you can use them within a CaseExpression, which can be used in a SelectExpression, and replicate the same behaviour:

->addSelect('CASE WHEN (organization.name LIKE :searchTerm) THEN 1 ELSE 0 END AS searchTermIsPartOfName')
->setParameter('searchTerm', "%{$sSearchTerm}%")

(there was also a minor issue about your LIKE expression - I'm pretty sure the % signs need to be part of the parameter value, not the query itself)

Doctrine DQL - Logic check in select

2 Answers