DDC-3364: QueryBuilder fails when using alias in having with like expr

doctrinebot commented 10 years ago

Jira issue originally created by user webdevilopers:

In my select I create an alias. I use a like expr on this alias in the having clause.

Trying several variations I still get the following error: Expected '.' or '(', got 'long_name'

My example including variations inside comments:

$having = $qb->expr()->like('long_name', $qb->expr()->literal('%' . $term . '%'));

$qb->select(array(
    'b.id', 'b.name AS branch_name',
    'c.name AS company_name',
    'GroupConcat(b.name, \', \', c.name) AS long*name' // same for 'c.name AS long*name'
))
->from('Application\Entity\Branch', 'b')
->join('b.company', 'c')
->where('b.compartment = 1')
->having('(' . $having . ')') // same for having($having)
->groupBy('c.id')
->orderBy('c.name')
->addOrderBy('b.name');

I use a Doctrine Extension for the MySQL _GROUPCONCAT functionality: https://github.com/beberlei/DoctrineExtensions/blob/master/lib/DoctrineExtensions/Query/Mysql/GroupConcat.php

This should have no effect on the result since I tried a simple alias - see comment - instead of it.

The problem seems to be using an alias inside the having clause when adding the like expr. The following clause would work:

$having = $qb->expr()->like('c.name', $qb->expr()->literal('%' . $term . '%'));

I also tried this workaround using the _GROUPCONCAT method inside the where part:

->andWhere($qb->expr()->like('GroupConcat(b.name, \', \', c.name)', $qb->expr()->literal('%' . $term . '%')))

Allthough I used the groupBy part I got this error: General error: 1111 Invalid use of group function

doctrinebot commented 10 years ago

Comment created by @ocramius:

What is the actual failure/exception type? What about the generated SQL?

doctrinebot commented 10 years ago

Comment created by @deeky666:

Moved to ORM, the error and use case is ORM related.

doctrinebot commented 9 years ago

Comment created by kryniol:

Same here. It has nothing in common with usage of GROUP_CONCAT. The simpliest example I can think of:

public function test()
{
    return $this->createQueryBuilder('b')
        ->select('b.id AS id')
        ->having('id LIKE :foobar')
        ->setParameter('foobar', 'foobar')
        ->getQuery()
        ->getResult();
}

This produces following error (full stacktrace from Symfony2 app):

{quote} [1] Doctrine\ORM\Query\QueryException: [Syntax Error] line 0, col 73: Error: Expected '.' or '(', got 'id' at n/a in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php line 52

at Doctrine\ORM\Query\QueryException::syntaxError('line 0, col 73: Error: Expected '.' or '(', got 'id'', object(QueryException))
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 448

at Doctrine\ORM\Query\Parser->syntaxError(''.' or '('')
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 2821

at Doctrine\ORM\Query\Parser->StringPrimary()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 2798

at Doctrine\ORM\Query\Parser->StringExpression()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 3085

at Doctrine\ORM\Query\Parser->LikeExpression()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 2478

at Doctrine\ORM\Query\Parser->SimpleConditionalExpression()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 2384

at Doctrine\ORM\Query\Parser->ConditionalPrimary()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 2360

at Doctrine\ORM\Query\Parser->ConditionalFactor()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 2328

at Doctrine\ORM\Query\Parser->ConditionalTerm()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 2303

at Doctrine\ORM\Query\Parser->ConditionalExpression()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 1339

at Doctrine\ORM\Query\Parser->HavingClause()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 878

at Doctrine\ORM\Query\Parser->SelectStatement()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 843

at Doctrine\ORM\Query\Parser->QueryLanguage()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 268

at Doctrine\ORM\Query\Parser->getAST()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 351

at Doctrine\ORM\Query\Parser->parse()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query.php line 255

at Doctrine\ORM\Query->_parse()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query.php line 267

at Doctrine\ORM\Query->_doExecute()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php line 794

at Doctrine\ORM\AbstractQuery->execute(null, '1')
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php line 574

at Doctrine\ORM\AbstractQuery->getResult()
    in /var/www/myproject/src/MyProject/MyBundle/Repository/MyEntityRepository.php line 94

at MyProject\MyBundle\Repository\MyEntityRepository->test()
    in /var/www/myproject/src/MyProject/MyEntityBundle/Controller/MyEntityController.php line 146

at MyProject\MyEntityBundle\Controller\MyEntityController->listResultAction(object(Request))
    in  line 

at call*user_func*array(array(object(MyEntityController), 'listResultAction'), array(object(Request)))
    in /var/www/myproject/app/bootstrap.php.cache line 3094

at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), '1')
    in /var/www/myproject/app/bootstrap.php.cache line 3056

at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), '1', true)
    in /var/www/myproject/app/bootstrap.php.cache line 3207

at Symfony\Component\HttpKernel\DependencyInjection\ContainerAwareHttpKernel->handle(object(Request), '1', true)
    in /var/www/myproject/app/bootstrap.php.cache line 2429

at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
    in /var/www/myproject/web/app_dev.php line 28

[2] Doctrine\ORM\Query\QueryException: SELECT b.id AS id FROM MyProject\MyBundle\Entity\MyEntity b HAVING id LIKE foobar at n/a in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php line 41

at Doctrine\ORM\Query\QueryException::dqlError('SELECT b.id AS id FROM MyProject\MyBundle\Entity\MyEntity b HAVING id LIKE foobar')
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 448

at Doctrine\ORM\Query\Parser->syntaxError(''.' or '('')
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 2821

at Doctrine\ORM\Query\Parser->StringPrimary()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 2798

at Doctrine\ORM\Query\Parser->StringExpression()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 3085

at Doctrine\ORM\Query\Parser->LikeExpression()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 2478

at Doctrine\ORM\Query\Parser->SimpleConditionalExpression()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 2384

at Doctrine\ORM\Query\Parser->ConditionalPrimary()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 2360

at Doctrine\ORM\Query\Parser->ConditionalFactor()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 2328

at Doctrine\ORM\Query\Parser->ConditionalTerm()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 2303

at Doctrine\ORM\Query\Parser->ConditionalExpression()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 1339

at Doctrine\ORM\Query\Parser->HavingClause()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 878

at Doctrine\ORM\Query\Parser->SelectStatement()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 843

at Doctrine\ORM\Query\Parser->QueryLanguage()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 268

at Doctrine\ORM\Query\Parser->getAST()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 351

at Doctrine\ORM\Query\Parser->parse()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query.php line 255

at Doctrine\ORM\Query->_parse()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/Query.php line 267

at Doctrine\ORM\Query->_doExecute()
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php line 794

at Doctrine\ORM\AbstractQuery->execute(null, '1')
    in /var/www/myproject/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php line 574

at Doctrine\ORM\AbstractQuery->getResult()
    in /var/www/myproject/src/MyProject/MyBundle/Repository/MyEntityRepository.php line 94

at MyProject\MyBundle\Repository\MyEntityRepository->test()
    in /var/www/myproject/src/MyProject/MyEntityBundle/Controller/MyEntityController.php line 146

at MyProject\MyEntityBundle\Controller\MyEntityController->listResultAction(object(Request))
    in  line 

at call*user_func*array(array(object(MyEntityController), 'listResultAction'), array(object(Request)))
    in /var/www/myproject/app/bootstrap.php.cache line 3094

at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), '1')
    in /var/www/myproject/app/bootstrap.php.cache line 3056

at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), '1', true)
    in /var/www/myproject/app/bootstrap.php.cache line 3207

at Symfony\Component\HttpKernel\DependencyInjection\ContainerAwareHttpKernel->handle(object(Request), '1', true)
    in /var/www/myproject/app/bootstrap.php.cache line 2429

at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
    in /var/www/myproject/web/app_dev.php line 28

{quote}

Note that the following example works like a charm:

public function test()
{
    return $this->createQueryBuilder('b')
        ->select('b.id AS id')
        ->having('id = :foobar')
        ->setParameter('foobar', 'foobar')
        ->getQuery()
        ->getResult();
}

Has anyone by any chance found any, even temporary workaround for this issue?

doctrinebot commented 9 years ago

Comment created by kryniol:

My temp solution is to use custom function which is being resolved to LIKE expression: https://gist.github.com/Kryniol/901255742078bdb8aa1d And the possible bug is that Doctrine\ORM\Query\Parser::LikeExpression() matches StringExpression instead of ArithmeticExpression (as for the ComparisonExpression) in the first part of LIKE. When changed to ArithmeticExpression in the Parser class itself, my code started working. But I'm not sure what this change could break...

doctrinebot commented 9 years ago

Comment created by Raphi:

I have the same issue. In DQL, it's possible to get around this bug by writing CONCAT in like condition too. For example : {quote}SELECT _, CONCAT('', field1, '') FROM _ GROUP BY ** HAVING CONCAT('', field1, '') LIKE **{quote} Please replace the stars with the right values.

qferr commented 8 years ago

Have you fix this bug ? I have the same error. Thank you.

kimpixel commented 8 years ago

Hi This Bug is still present!

The custom like extension from "kryniol" is also not working or i unable to setup it right. And the workaround from "Raphi" is no option for me (have some auto generated code)

can someone help to get this working?

I am in the Flow 3.0 Framework: $this->getEntityManager()->getConfiguration()->addCustomStringFunction('like', 'XXX\YYYYY\Doctrine\Functions\Like');

Ocramius commented 8 years ago

Write a failing test case, then the fix becomes easier to find.

kimpixel commented 8 years ago

hi the bug is very simple to reproduce as "kryniol" wrote above

public function test()
{
    return $this->createQueryBuilder('b')
        ->select('b.id AS id')
        ->having('id LIKE :foobar')
        ->setParameter('foobar', 'foobar')
        ->getQuery()
        ->getResult();
}

Ocramius commented 8 years ago

Yes, which means that it is simple to send a pull request with a failing test case ;-)

romaricdrigon commented 5 years ago

I just tried as I was investing #7846, I could not reproduce.

doctrine / orm

DDC-3364: QueryBuilder fails when using alias in having with like expr #4164