Can not represent query with subquery, that has group by and alias

[andrew-svirin]

I am trying to represent not complex query with doctrine and fail. I suspect that Doctrine has small defect.

PgSQL:

SELECT * FROM app_locations l 
WHERE  LOWER(l.title) IN ( 
SELECT LOWER(sl.title) as t FROM app_locations sl 
GROUP BY t 
HAVING COUNT(sl.id) > 1
)

Doctrine:

 $qb = $this->createQueryBuilder('l')
            ->select()
            ->where(
                $expr->in('LOWER(l.title)',
                    $this->createQueryBuilder('sl')
                        ->select('LOWER(sl.title) as t')
                        ->groupBy('t')
                        ->having(
                            $expr->gt($expr->count('sl.id'), 1)
                        )
                        ->getDQL()
                )
            );
$qb->getQuery()->execute();

Error is: (it is not informative error, but its point on LOWER(sl.title) as t )

[Syntax Error] line 0, col 85: Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got 'as'

[ostrolucky]

Moving, not a DoctrineBundle issue

[SenseException]

@andrew-svirin Can you please also show the resulting DQL of this querybuilder?

[andrew-svirin]

I can not finish building, because get error $qb->getQuery()->execute(); this line does not reach:

[Syntax Error] line 0, col 85: Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got 'as'

So i also can not get DQL

[faizanakram99]

@andrew-svirin

You can do $qb->getQuery()->getDQL() to see the generated DQL and then paste it here.