PROBLEM: Doctrine Query + Like

New topics about Symfony 2 should go here

Moderators: tiagojsag, dcobalt

PROBLEM: Doctrine Query + Like

Postby lrc » Wed Jun 06, 2012 3:27 pm

I have problems to use a query with LIKE expression

QUERY:
Code: Select all
$dql = "SELECT u FROM Users u JOIN u.group g WHERE g.name LIKE lower('ADMIN')";
$query = $em->createQuery($dql);
$result = $query->getResult();


ERROR:
Code: Select all
QueryException: [Syntax Error] line 0, col 147: Error: Expected Doctrine\ORM\Query\Lexer::T_STRING, got 'lower'
lrc
Junior Member
 
Posts: 6
Joined: Tue Feb 14, 2012 1:55 pm

Re: PROBLEM: Doctrine Query + Like

Postby dreipunktnull » Wed Jun 06, 2012 4:12 pm

As the error message states LIKE expects a string so the query should read

Code: Select all
SELECT u FROM Users u JOIN u.group g WHERE g.name LIKE 'admin'


Queries are case insensitive anyways if the collation is utf8_general_ci.
User avatar
dreipunktnull
Faithful Member
 
Posts: 1244
Joined: Fri Jun 17, 2011 10:03 am
Location: Germany

Re: PROBLEM: Doctrine Query + Like

Postby lrc » Wed Jun 06, 2012 4:20 pm

Ok, but I need to use other functions, LOWER was just an example.

Code: Select all
SELECT u FROM Users u JOIN u.group g WHERE g.name LIKE xyz('ADMIN')


How can I change LikeExpression to support my needs?
lrc
Junior Member
 
Posts: 6
Joined: Tue Feb 14, 2012 1:55 pm

Re: PROBLEM: Doctrine Query + Like

Postby dreipunktnull » Wed Jun 06, 2012 5:04 pm

Why don't you preprocess the value in PHP?

Code: Select all
$like = someFunction('ADMIN');

$dql = "SELECT u FROM Users u JOIN u.group g WHERE g.name LIKE :like";
$query = $em->createQuery($dql)->setParameter('like', $like);
$result = $query->getResult();
User avatar
dreipunktnull
Faithful Member
 
Posts: 1244
Joined: Fri Jun 17, 2011 10:03 am
Location: Germany

Re: PROBLEM: Doctrine Query + Like

Postby lrc » Wed Jun 06, 2012 5:58 pm

Ok, but how can I process the left side of comparison?

$original_name = "ÁDMIN"
$unnacent_name = "ADMIN";
$lower_name = "admin";

$dql = SELECT u From Users u JOIN u.group g WHERE LOWER ( MY_DB_UNNACENT_FUNCTION ( g.name ) ) LIKE :lower_name";
$query = $em->createQuery($dql);
$query->setParameter("lower_name", "%" . $lower_name . "%");
$result = $query->getResult();
lrc
Junior Member
 
Posts: 6
Joined: Tue Feb 14, 2012 1:55 pm

Re: PROBLEM: Doctrine Query + Like

Postby althaus » Thu Jun 21, 2012 4:07 pm

Doctrine only supports a bunch of functions out-of-the-box. If you wanna use special db functions, you have to create a custom DQL function:

http://docs.doctrine-project.org/projec ... tions.html
http://symfony.com/doc/current/cookbook ... tions.html
althaus
Faithful Member
 
Posts: 605
Joined: Tue Oct 18, 2011 2:20 pm
Location: Germany


Return to General Symfony 2 discussion

Who is online

Users browsing this forum: Google [Bot], Google Feedfetcher and 9 guests