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

New Issue

admin · 2026-01-22T15:32:16+01:00

admin commented

2026-01-22 15:32:16 +01:00

Originally created by @andrew-svirin on GitHub (Jan 28, 2020).

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'

Originally created by @andrew-svirin on GitHub (Jan 28, 2020). I am trying to represent not complex query with doctrine and fail. I suspect that Doctrine has small defect. PgSQL: ```sql 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: ```php $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' ```

admin commented

2026-01-22 15:32:17 +01:00

@ostrolucky commented on GitHub (Jan 28, 2020):

Moving, not a DoctrineBundle issue

@ostrolucky commented on GitHub (Jan 28, 2020): Moving, not a DoctrineBundle issue

admin commented

2026-01-22 15:32:18 +01:00

@SenseException commented on GitHub (Jan 28, 2020):

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

@SenseException commented on GitHub (Jan 28, 2020): @andrew-svirin Can you please also show the resulting DQL of this querybuilder?

admin commented

2026-01-22 15:32:18 +01:00

@andrew-svirin commented on GitHub (Jan 29, 2020):

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

@andrew-svirin commented on GitHub (Jan 29, 2020): 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

admin commented

2026-01-22 15:32:18 +01:00

@faizanakram99 commented on GitHub (Feb 23, 2021):

@andrew-svirin

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

@faizanakram99 commented on GitHub (Feb 23, 2021): @andrew-svirin You can do `$qb->getQuery()->getDQL()` to see the generated DQL and then paste it here.

admin referenced this issue

2026-01-22 16:06:00 +01:00

[PR #6392] Correct DQL `INSTANCE OF` to filter all possible child classes #9963

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#6392