SubQuery in addSelect with Alias #6408

New Issue

Closed

opened 2026-01-22 15:32:44 +01:00 by admin · 3 comments

admin commented 2026-01-22 15:32:44 +01:00

Owner

Copy Link

Originally created by @ybenhssaien on GitHub (Feb 21, 2020).

Bug Report

Q	A
BC Break	yes
Version	2.7

Summary

Creating a subquery in addSelect with alias, generates syntax error while it shouldn't

Current behavior

Working with QueryBuilder to construct a parent query with subqueries in columns :

• The first case : subqueries with alias (not working)

        $qb = $this->getMissionQueryBuilder()->select('DISTINCT m.cmi');

        foreach (MissionHelper::getTypeOfAssignementList() as $key => $typeOfAssignement) {
            $subQbAlias = 'sub_m_'.$key;

            $subQb = $this
                ->createQueryBuilder($subQbAlias)
                ->select('count('.$subQbAlias.')');

            $subQb
                ->where($subQbAlias.'.cmi = m.cmi')
                ->andWhere($subQb->expr()->like(
                    $subQbAlias.'.typeOfAssignement',
                    $qb->expr()->literal($typeOfAssignement)
                ));

            $qb->addSelect(sprintf(
                '(%1s) AS %2s',
                $subQb->getQuery()->getDQL(),
                $subQb->expr()->literal($typeOfAssignement)
            ));
        }

        return $qb->getQuery()->getResult();

• The second case : subqueries without alias (working)

        $qb = $this->getMissionQueryBuilder()->select('DISTINCT m.cmi');

        foreach (MissionHelper::getTypeOfAssignementList() as $key => $typeOfAssignement) {
            $subQbAlias = 'sub_m_'.$key;

            $subQb = $this
                ->createQueryBuilder($subQbAlias)
                ->select('count('.$subQbAlias.')');

            $subQb
                ->where($subQbAlias.'.cmi = m.cmi')
                ->andWhere($subQb->expr()->like(
                    $subQbAlias.'.typeOfAssignement',
                    $qb->expr()->literal($typeOfAssignement)
                ));

            $qb->addSelect(sprintf(
                '(%1s)',
                $subQb->getQuery()->getDQL()
            ));
        }

        return $qb->getQuery()->getResult();

Look at Excepted Behavior for examples

How to reproduce

Create subqueries in select columns with alias.

Expected behavior

• Generated DQL with alias (not working) :

SELECT 
            DISTINCT m.cmi, 
    (SELECT count(sub_m_0) FROM App\Entity\Mission sub_m_0 WHERE sub_m_0.cmi = m.cmi AND sub_m_0.typeOfAssignement LIKE 'APPRENTI') AS 'APPRENTI', 
    (SELECT count(sub_m_1) FROM App\Entity\Mission sub_m_1 WHERE sub_m_1.cmi = m.cmi AND sub_m_1.typeOfAssignement LIKE 'DUAL') AS 'DUAL', 
    (SELECT count(sub_m_2) FROM App\Entity\Mission sub_m_2 WHERE sub_m_2.cmi = m.cmi AND sub_m_2.typeOfAssignement LIKE 'EXCEPTION') AS 'EXCEPTION', 
    (SELECT count(sub_m_3) FROM App\Entity\Mission sub_m_3 WHERE sub_m_3.cmi = m.cmi AND sub_m_3.typeOfAssignement LIKE 'HOME BASED') AS 'HOME BASED', 
    (SELECT count(sub_m_4) FROM App\Entity\Mission sub_m_4 WHERE sub_m_4.cmi = m.cmi AND sub_m_4.typeOfAssignement LIKE 'PERMANENT') AS 'PERMANENT', 
    (SELECT count(sub_m_5) FROM App\Entity\Mission sub_m_5 WHERE sub_m_5.cmi = m.cmi AND sub_m_5.typeOfAssignement LIKE 'SHORT TERM') AS 'SHORT TERM', 
    (SELECT count(sub_m_6) FROM App\Entity\Mission sub_m_6 WHERE sub_m_6.cmi = m.cmi AND sub_m_6.typeOfAssignement LIKE 'VIE') AS 'VIE' 
FROM App\Entity\Mission m
;

• Generated DQL without Aliases (Working) :

SELECT 
            DISTINCT a0_.cmi AS cmi_0, 
    (SELECT count(a1_.id) AS sclr_2 FROM atea_mission a1_ WHERE a1_.cmi = a0_.cmi AND a1_.type_of_assignement LIKE 'APPRENTI') AS sclr_1, 
    (SELECT count(a2_.id) AS sclr_4 FROM atea_mission a2_ WHERE a2_.cmi = a0_.cmi AND a2_.type_of_assignement LIKE 'DUAL') AS sclr_3, 
    (SELECT count(a3_.id) AS sclr_6 FROM atea_mission a3_ WHERE a3_.cmi = a0_.cmi AND a3_.type_of_assignement LIKE 'EXCEPTION') AS sclr_5, 
    (SELECT count(a4_.id) AS sclr_8 FROM atea_mission a4_ WHERE a4_.cmi = a0_.cmi AND a4_.type_of_assignement LIKE 'HOME BASED') AS sclr_7, 
    (SELECT count(a5_.id) AS sclr_10 FROM atea_mission a5_ WHERE a5_.cmi = a0_.cmi AND a5_.type_of_assignement LIKE 'PERMANENT') AS sclr_9, 
    (SELECT count(a6_.id) AS sclr_12 FROM atea_mission a6_ WHERE a6_.cmi = a0_.cmi AND a6_.type_of_assignement LIKE 'SHORT TERM') AS sclr_11, 
    (SELECT count(a7_.id) AS sclr_14 FROM atea_mission a7_ WHERE a7_.cmi = a0_.cmi AND a7_.type_of_assignement LIKE 'VIE') AS sclr_13 
FROM atea_mission a0_ 
;

• MySQL equivalent query ;

SELECT
            distinct cmi as CMI,
            (select count(id) from atea_mission as sub_mission where sub_mission.cmi = mission.cmi and type_of_assignement LIKE 'VIE' ) as 'VIE',
            (select count(id) from atea_mission as sub_mission where sub_mission.cmi = mission.cmi and type_of_assignement LIKE 'PERMANENT' ) as 'PERMANENT',
            (select count(id) from atea_mission as sub_mission where sub_mission.cmi = mission.cmi and type_of_assignement LIKE 'DUAL' ) as 'DUAL',
            (select count(id) from atea_mission as sub_mission where sub_mission.cmi = mission.cmi and type_of_assignement LIKE 'APPRENTI' ) as 'APPRENTI',
            (select count(id) from atea_mission as sub_mission where sub_mission.cmi = mission.cmi and type_of_assignement LIKE 'HOME BASED' ) as 'HOME BASED',
            (select count(id) from atea_mission as sub_mission where sub_mission.cmi = mission.cmi and type_of_assignement LIKE 'SHORT TERM' ) as 'SHORT TERM'
FROM atea_mission as mission

Originally created by @ybenhssaien on GitHub (Feb 21, 2020). ### Bug Report <!-- Fill in the relevant information below to help triage your issue. --> | Q | A |------------ | ------ | BC Break | yes | Version | 2.7 #### Summary Creating a subquery in addSelect with alias, generates syntax error while it shouldn't  #### Current behavior Working with QueryBuilder to construct a parent query with subqueries in columns : - The first case : subqueries with alias (not working) ```php $qb = $this->getMissionQueryBuilder()->select('DISTINCT m.cmi'); foreach (MissionHelper::getTypeOfAssignementList() as $key => $typeOfAssignement) { $subQbAlias = 'sub_m_'.$key; $subQb = $this ->createQueryBuilder($subQbAlias) ->select('count('.$subQbAlias.')'); $subQb ->where($subQbAlias.'.cmi = m.cmi') ->andWhere($subQb->expr()->like( $subQbAlias.'.typeOfAssignement', $qb->expr()->literal($typeOfAssignement) )); $qb->addSelect(sprintf( '(%1s) AS %2s', $subQb->getQuery()->getDQL(), $subQb->expr()->literal($typeOfAssignement) )); } return $qb->getQuery()->getResult(); ``` - The second case : subqueries without alias (working) ```php $qb = $this->getMissionQueryBuilder()->select('DISTINCT m.cmi'); foreach (MissionHelper::getTypeOfAssignementList() as $key => $typeOfAssignement) { $subQbAlias = 'sub_m_'.$key; $subQb = $this ->createQueryBuilder($subQbAlias) ->select('count('.$subQbAlias.')'); $subQb ->where($subQbAlias.'.cmi = m.cmi') ->andWhere($subQb->expr()->like( $subQbAlias.'.typeOfAssignement', $qb->expr()->literal($typeOfAssignement) )); $qb->addSelect(sprintf( '(%1s)', $subQb->getQuery()->getDQL() )); } return $qb->getQuery()->getResult(); ``` > Look at Excepted Behavior for examples #### How to reproduce Create subqueries in select columns with alias. #### Expected behavior - Generated DQL with alias (not working) : ```mysql SELECT DISTINCT m.cmi, (SELECT count(sub_m_0) FROM App\Entity\Mission sub_m_0 WHERE sub_m_0.cmi = m.cmi AND sub_m_0.typeOfAssignement LIKE 'APPRENTI') AS 'APPRENTI', (SELECT count(sub_m_1) FROM App\Entity\Mission sub_m_1 WHERE sub_m_1.cmi = m.cmi AND sub_m_1.typeOfAssignement LIKE 'DUAL') AS 'DUAL', (SELECT count(sub_m_2) FROM App\Entity\Mission sub_m_2 WHERE sub_m_2.cmi = m.cmi AND sub_m_2.typeOfAssignement LIKE 'EXCEPTION') AS 'EXCEPTION', (SELECT count(sub_m_3) FROM App\Entity\Mission sub_m_3 WHERE sub_m_3.cmi = m.cmi AND sub_m_3.typeOfAssignement LIKE 'HOME BASED') AS 'HOME BASED', (SELECT count(sub_m_4) FROM App\Entity\Mission sub_m_4 WHERE sub_m_4.cmi = m.cmi AND sub_m_4.typeOfAssignement LIKE 'PERMANENT') AS 'PERMANENT', (SELECT count(sub_m_5) FROM App\Entity\Mission sub_m_5 WHERE sub_m_5.cmi = m.cmi AND sub_m_5.typeOfAssignement LIKE 'SHORT TERM') AS 'SHORT TERM', (SELECT count(sub_m_6) FROM App\Entity\Mission sub_m_6 WHERE sub_m_6.cmi = m.cmi AND sub_m_6.typeOfAssignement LIKE 'VIE') AS 'VIE' FROM App\Entity\Mission m ; ``` - Generated DQL without Aliases (Working) : ```mysql SELECT DISTINCT a0_.cmi AS cmi_0, (SELECT count(a1_.id) AS sclr_2 FROM atea_mission a1_ WHERE a1_.cmi = a0_.cmi AND a1_.type_of_assignement LIKE 'APPRENTI') AS sclr_1, (SELECT count(a2_.id) AS sclr_4 FROM atea_mission a2_ WHERE a2_.cmi = a0_.cmi AND a2_.type_of_assignement LIKE 'DUAL') AS sclr_3, (SELECT count(a3_.id) AS sclr_6 FROM atea_mission a3_ WHERE a3_.cmi = a0_.cmi AND a3_.type_of_assignement LIKE 'EXCEPTION') AS sclr_5, (SELECT count(a4_.id) AS sclr_8 FROM atea_mission a4_ WHERE a4_.cmi = a0_.cmi AND a4_.type_of_assignement LIKE 'HOME BASED') AS sclr_7, (SELECT count(a5_.id) AS sclr_10 FROM atea_mission a5_ WHERE a5_.cmi = a0_.cmi AND a5_.type_of_assignement LIKE 'PERMANENT') AS sclr_9, (SELECT count(a6_.id) AS sclr_12 FROM atea_mission a6_ WHERE a6_.cmi = a0_.cmi AND a6_.type_of_assignement LIKE 'SHORT TERM') AS sclr_11, (SELECT count(a7_.id) AS sclr_14 FROM atea_mission a7_ WHERE a7_.cmi = a0_.cmi AND a7_.type_of_assignement LIKE 'VIE') AS sclr_13 FROM atea_mission a0_ ; ``` - MySQL equivalent query ; ```mysql SELECT distinct cmi as CMI, (select count(id) from atea_mission as sub_mission where sub_mission.cmi = mission.cmi and type_of_assignement LIKE 'VIE' ) as 'VIE', (select count(id) from atea_mission as sub_mission where sub_mission.cmi = mission.cmi and type_of_assignement LIKE 'PERMANENT' ) as 'PERMANENT', (select count(id) from atea_mission as sub_mission where sub_mission.cmi = mission.cmi and type_of_assignement LIKE 'DUAL' ) as 'DUAL', (select count(id) from atea_mission as sub_mission where sub_mission.cmi = mission.cmi and type_of_assignement LIKE 'APPRENTI' ) as 'APPRENTI', (select count(id) from atea_mission as sub_mission where sub_mission.cmi = mission.cmi and type_of_assignement LIKE 'HOME BASED' ) as 'HOME BASED', (select count(id) from atea_mission as sub_mission where sub_mission.cmi = mission.cmi and type_of_assignement LIKE 'SHORT TERM' ) as 'SHORT TERM' FROM atea_mission as mission ```

admin closed this issue 2026-01-22 15:32:45 +01:00

admin commented 2026-01-22 15:32:45 +01:00

Author

Owner

Copy Link

@ybenhssaien commented on GitHub (Feb 26, 2020):

Solution : without using$qb->expr()->literal(), only assert that all whitespace characters are replace by a non withespace character like _ using preg_replace or equivalent :

$qb->addSelect(sprintf(
            '(%1s) AS %2s',
            $subQb->getQuery()->getDQL(),
            preg_replace('/\s/', '_', $typeOfAssignement)
        ));

@ybenhssaien commented on GitHub (Feb 26, 2020): **Solution :** without using`$qb->expr()->literal()`, only assert that all whitespace characters are replace by a non withespace character like `_` using `preg_replace` or equivalent : ```php $qb->addSelect(sprintf( '(%1s) AS %2s', $subQb->getQuery()->getDQL(), preg_replace('/\s/', '_', $typeOfAssignement) )); ```

admin commented 2026-01-22 15:32:46 +01:00

Author

Owner

Copy Link

@beberlei commented on GitHub (Mar 1, 2020):

Yes, the bug is that you used literal for the aliases, they are not literals.

@beberlei commented on GitHub (Mar 1, 2020): Yes, the bug is that you used literal for the aliases, they are not literals.

admin commented 2026-01-22 15:32:46 +01:00

Author

Owner

Copy Link

@ybenhssaien commented on GitHub (Mar 2, 2020):

Even without literal is not working :

$qb = $this->getMissionQueryBuilder()->select('DISTINCT m.cmi');

        foreach (MissionHelper::getTypeOfAssignementList() as $key => $typeOfAssignement) {
            $subQbAlias = 'sub_m_'.$key;

            $subQb = $this
                ->createQueryBuilder($subQbAlias)
                ->select('count('.$subQbAlias.')');

            $subQb
                ->where($subQbAlias.'.cmi = m.cmi')
                ->andWhere($subQb->expr()->like(
                    $subQbAlias.'.typeOfAssignement',
                    $qb->expr()->literal($typeOfAssignement)
                ));

            $qb->addSelect(sprintf(
                '(%1s) AS %2s',
                $subQb->getQuery()->getDQL(),
                $typeOfAssignement
            ));
        }

        return $qb->getQuery()->getResult();

So it is a bug !

@ybenhssaien commented on GitHub (Mar 2, 2020): Even without literal is not working : ```php $qb = $this->getMissionQueryBuilder()->select('DISTINCT m.cmi'); foreach (MissionHelper::getTypeOfAssignementList() as $key => $typeOfAssignement) { $subQbAlias = 'sub_m_'.$key; $subQb = $this ->createQueryBuilder($subQbAlias) ->select('count('.$subQbAlias.')'); $subQb ->where($subQbAlias.'.cmi = m.cmi') ->andWhere($subQb->expr()->like( $subQbAlias.'.typeOfAssignement', $qb->expr()->literal($typeOfAssignement) )); $qb->addSelect(sprintf( '(%1s) AS %2s', $subQb->getQuery()->getDQL(), $typeOfAssignement )); } return $qb->getQuery()->getResult(); ```  So it is a bug !

admin referenced this issue 2026-01-22 16:06:02 +01:00

[PR #6408] ID generators require concrete EntityManager instance instead of EntityManagerInterface #9973

Sign in to join this conversation.

No Branch/Tag Specified

Branches Tags

3.6.x

2.20.x

dependabot/github_actions/2.20.x/doctrine/dot-github/dot-github/workflows/composer-lint.yml-14.0.0

3.7.x

4.0.x

2.21.x

old-prototype-3.x

3.6.2

3.6.1

3.6.0

2.20.9

3.5.8

3.5.7

3.5.6

3.5.5

2.20.8

3.5.4

3.5.3

2.20.7

3.5.2

2.20.6

3.5.1

3.5.0

3.4.4

3.4.3

3.4.2

2.20.5

3.4.1

3.4.0

3.3.4

2.20.4

3.3.3

2.20.3

3.3.2

2.20.2

3.3.1

2.20.1

3.3.0

3.2.3

2.20.0

2.19.8

3.2.2

2.19.7

3.2.1

2.19.6

3.2.0

3.1.4

3.1.3

2.19.5

3.1.2

2.19.4

3.1.1

2.19.3

2.19.2

2.19.1

3.1.0

2.19.0

3.0.3

2.18.3

3.0.2

2.18.2

3.0.1

2.18.1

3.0.0

2.18.0

2.17.5

3.0.0-RC1

2.17.4

2.17.3

2.17.2

2.17.1

3.0.0-beta2

2.17.0

2.16.3

3.0.0-beta1

2.16.2

2.16.1

2.16.0

2.15.5

2.15.4

2.15.3

2.15.2

2.15.1

2.15.0

2.14.3

2.14.2

2.14.1

2.14.0

2.13.5

2.13.4

2.13.3

2.13.2

2.13.1

2.13.0

2.12.4

2.12.3

2.12.2

2.12.1

2.12.0

2.11.3

2.11.2

2.11.1

2.11.0

2.10.5

2.10.4

2.10.3

2.10.2

2.10.1

2.10.0

2.9.6

2.9.5

2.9.4

2.9.3

2.9.2

2.9.1

2.9.0

2.8.5

2.8.4

2.8.3

2.8.2

2.8.1

2.8.0

2.7.5

2.7.4

v2.7.3

v2.7.2

v2.7.1

v2.7.0

v2.6.6

v2.6.5

v2.6.4

v2.6.3

v2.6.2

v2.6.1

v2.6.0

v2.5.14

v2.5.13

v2.5.12

v2.5.11

v2.5.10

v2.5.9

v2.5.8

v2.5.7

v2.5.6

v2.5.5

v2.5.4

v2.5.3

v2.5.2

v2.4.8

v2.5.1

v2.5.0

v2.5.0-RC2

v2.5.0-RC1

v2.5.0-beta1

v2.5.0-alpha2

v2.5.0-alpha1

v2.4.7

v2.4.6

v2.4.5

v2.4.4

v2.4.3

v2.3.6

v2.4.2

2.3.5

v2.4.1

v2.4.0

2.4.0-RC2

2.4.0-RC1

2.4.0-BETA2

2.3.4

2.3.3

2.4.0-BETA1

2.3.2

2.3.1

2.3.0

2.3.0-RC4

2.3.0-RC3

2.3.0-RC2

2.2.3

2.3.0-RC1

2.3.0-BETA1

2.1.7

2.2.2

2.2.1

2.1.6

2.2.0

2.2.0-RC1

2.2.0-BETA2

2.2.0-BETA1

2.1.5

2.1.4

2.1.3

2.1.2

2.1.1

2.0.7

2.1.0

2.1.0RC3

2.1.0RC2

2.1.0RC1

2.0.6

2.1.0BETA1

2.0.5

2.0.4

2.0.3

2.0.2

2.0.1

2.0.0

2.0.0RC2

2.0.0RC1

2.0.0-BETA4

2.0.0-BETA3

2.0.0-BETA2

2.0.0-BETA1

Labels

Clear labels

BC Break

Bug

Can't Fix

Deprecation

Documentation

DQL

Duplicate

Failing Test

Hacktoberfest

Improvement

Improvement

Incomplete

Invalid

Missing Tests

New Feature

PR Created

pull-request

Mirrored from GitHub Pull Request

Question

Regression

Status: Waiting feedback

Waiting feedback

WIP

Won't Fix

No Label

Milestone

No items

No Milestone

Projects

Clear projects

No project

Assignees

Clear assignees

admin

No Assignees

1 Participants

Notifications

Subscribe

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#6408