doctrine/archived-orm
1
0
Fork 0
mirror of https://github.com/doctrine/orm.git synced 2026-03-23 22:42:18 +01:00
Code Issues 5.3k Packages Projects Releases 100 Wiki Activity

Embeddables do not properly alias for order-by statements in query builder #5210

New Issue
Closed
opened 2026-01-22 15:01:38 +01:00 by admin · 4 comments
admin commented 2026-01-22 15:01:38 +01:00
Owner
Copy Link

Originally created by @jackdpeterson on GitHub (Aug 5, 2016).

If one has an entity that contains an embeddable (referenced as $priceObj):

/**
 * @ORM\Embeddable
 */
class Price
{

    const CUR_USD = 'USD';

    const CUR_CAD = 'CAD';

    const INVALID_CURRENCY_CODE_EXC_CODE = 2;

    const INVALID_CURRENCY_CODE_EXC_MESSAGE = 'An invalided currency code was provided.';

    protected $validCurrencies = array(
        0 => self::CUR_USD,
        1 => self::CUR_CAD
    );

    /**
     * @ORM\Column(type="bigint")
     */
    private $amount;

    /**
     * @ORM\Column(type="string", length=3, nullable=false)
     */
    private $currencyCode;

}

And one attempts to perform an orderBy on the amount field. Doctrine generates a proper-looking DQL statement; however, the SQL statement where the order-by'd element is using the pre-aliased select statement. This appears to cause a 'semantical error'.

PHP:

$qb = $this->_em->createQueryBuilder();
        $query = $qb->select('row')->from('ExampleDomainModel\Entity\Barcode\ProductResult', 'row');
        $query->andWhere($qb->expr()
            ->eq('row.GSIN', ':GSIN'))
            ->setParameter('GSIN', $barcode);

        $query->addOrderBy('row.priceObj.amount', 'asc');

Generated DQL:

SELECT row FROM ExampleDomainModel\Entity\Barcode\ProductResult row WHERE row.GSIN = :GSIN AND row.isNew = :a57a4da9e5537d AND row.priceObj.amount >= :a57a4da9e5677f ORDER BY row.priceObj.amount asc

Generated SQL:

SELECT b0_.id AS id_0, b0_.feedProvider AS feedProvider_1, b0_.isNew AS isNew_2, b0_.title AS title_3, b0_.description AS description_4, b0_.GSIN AS GSIN_5, b0_.createdAt AS createdAt_6, b0_.url AS url_7, b0_.imageUrl AS imageUrl_8, b0_.fpExtendedAttributes AS fpExtendedAttributes_9, b0_.priceObj_amount AS priceObj_amount_10, b0_.priceObj_currencyCode AS priceObj_currencyCode_11, b0_.location_latitude AS location_latitude_12, b0_.location_longitude AS location_longitude_13 FROM barcode_product_result b0_ WHERE b0_.GSIN = ? AND b0_.isNew = ? AND b0_.priceObj_amount >= ? ORDER BY b0_.priceObj_amount ASC

$ php composer.phar show --installed | grep doctrine

adamlundrigan/ldc-zf-oauth2-doctrine         dev-master 54c810e zf-oauth2 adapter for use with D...
api-skeletons/zf-oauth2-doctrine             1.0.5              Doctrine OAuth2 Server Adapter f...
doctrine/annotations                         v1.2.7             Docblock Annotations Parser
doctrine/cache                               v1.6.0             Caching library offering an obje...
doctrine/collections                         v1.3.0             Collections Abstraction library
doctrine/common                              v2.5.3             Common Library for Doctrine proj...
doctrine/data-fixtures                       v1.0.2             Data Fixtures for all Doctrine O...
doctrine/dbal                                v2.5.4             Database Abstraction Layer
doctrine/doctrine-module                     0.9.0              Zend Framework 2 Module that pro...
doctrine/doctrine-orm-module                 0.9.2              Zend Framework 2 Module that pro...
doctrine/inflector                           v1.1.0             Common String Manipulations with...
doctrine/instantiator                        1.0.5              A small, lightweight utility to ...
doctrine/lexer                               v1.0.1             Base library for a lexer that ca...
doctrine/orm                                 v2.5.4             Object-Relational-Mapper for PHP
hounddog/doctrine-data-fixture-module        0.0.4              Zend Framework 2 Module that pro...
phpro/zf-doctrine-hydration-module           0.1.5              Doctrine hydrators for ZF2
zfcampus/zf-apigility-doctrine               0.4.1              Doctrine for Apigility
zfcampus/zf-doctrine-querybuilder            1.4.2              QueryBuilder filters for Doctrine
Originally created by @jackdpeterson on GitHub (Aug 5, 2016). If one has an entity that contains an embeddable (referenced as $priceObj): ``` /** * @ORM\Embeddable */ class Price { const CUR_USD = 'USD'; const CUR_CAD = 'CAD'; const INVALID_CURRENCY_CODE_EXC_CODE = 2; const INVALID_CURRENCY_CODE_EXC_MESSAGE = 'An invalided currency code was provided.'; protected $validCurrencies = array( 0 => self::CUR_USD, 1 => self::CUR_CAD ); /** * @ORM\Column(type="bigint") */ private $amount; /** * @ORM\Column(type="string", length=3, nullable=false) */ private $currencyCode; } ``` And one attempts to perform an orderBy on the amount field. Doctrine generates a proper-looking DQL statement; however, the SQL statement where the order-by'd element is using the pre-aliased select statement. This appears to cause a 'semantical error'. PHP: ``` $qb = $this->_em->createQueryBuilder(); $query = $qb->select('row')->from('ExampleDomainModel\Entity\Barcode\ProductResult', 'row'); $query->andWhere($qb->expr() ->eq('row.GSIN', ':GSIN')) ->setParameter('GSIN', $barcode); $query->addOrderBy('row.priceObj.amount', 'asc'); ``` Generated DQL: ``` SELECT row FROM ExampleDomainModel\Entity\Barcode\ProductResult row WHERE row.GSIN = :GSIN AND row.isNew = :a57a4da9e5537d AND row.priceObj.amount >= :a57a4da9e5677f ORDER BY row.priceObj.amount asc ``` Generated SQL: ``` SELECT b0_.id AS id_0, b0_.feedProvider AS feedProvider_1, b0_.isNew AS isNew_2, b0_.title AS title_3, b0_.description AS description_4, b0_.GSIN AS GSIN_5, b0_.createdAt AS createdAt_6, b0_.url AS url_7, b0_.imageUrl AS imageUrl_8, b0_.fpExtendedAttributes AS fpExtendedAttributes_9, b0_.priceObj_amount AS priceObj_amount_10, b0_.priceObj_currencyCode AS priceObj_currencyCode_11, b0_.location_latitude AS location_latitude_12, b0_.location_longitude AS location_longitude_13 FROM barcode_product_result b0_ WHERE b0_.GSIN = ? AND b0_.isNew = ? AND b0_.priceObj_amount >= ? ORDER BY b0_.priceObj_amount ASC ``` $ php composer.phar show --installed | grep doctrine ``` adamlundrigan/ldc-zf-oauth2-doctrine dev-master 54c810e zf-oauth2 adapter for use with D... api-skeletons/zf-oauth2-doctrine 1.0.5 Doctrine OAuth2 Server Adapter f... doctrine/annotations v1.2.7 Docblock Annotations Parser doctrine/cache v1.6.0 Caching library offering an obje... doctrine/collections v1.3.0 Collections Abstraction library doctrine/common v2.5.3 Common Library for Doctrine proj... doctrine/data-fixtures v1.0.2 Data Fixtures for all Doctrine O... doctrine/dbal v2.5.4 Database Abstraction Layer doctrine/doctrine-module 0.9.0 Zend Framework 2 Module that pro... doctrine/doctrine-orm-module 0.9.2 Zend Framework 2 Module that pro... doctrine/inflector v1.1.0 Common String Manipulations with... doctrine/instantiator 1.0.5 A small, lightweight utility to ... doctrine/lexer v1.0.1 Base library for a lexer that ca... doctrine/orm v2.5.4 Object-Relational-Mapper for PHP hounddog/doctrine-data-fixture-module 0.0.4 Zend Framework 2 Module that pro... phpro/zf-doctrine-hydration-module 0.1.5 Doctrine hydrators for ZF2 zfcampus/zf-apigility-doctrine 0.4.1 Doctrine for Apigility zfcampus/zf-doctrine-querybuilder 1.4.2 QueryBuilder filters for Doctrine ```
admin added the BugMissing Tests labels 2026-01-22 15:01:39 +01:00
admin closed this issue 2026-01-22 15:01:39 +01:00
admin commented 2026-01-22 15:01:40 +01:00
Author
Owner
Copy Link

@jackdpeterson commented on GitHub (Aug 5, 2016):

Expected:

SELECT b0_.id AS id_0, b0_.feedProvider AS feedProvider_1, b0_.isNew AS isNew_2, b0_.title AS title_3, b0_.description AS description_4, b0_.GSIN AS GSIN_5, b0_.createdAt AS createdAt_6, b0_.url AS url_7, b0_.imageUrl AS imageUrl_8, b0_.fpExtendedAttributes AS fpExtendedAttributes_9, b0_.priceObj_amount AS priceObj_amount_10, b0_.priceObj_currencyCode AS priceObj_currencyCode_11, b0_.location_latitude AS location_latitude_12, b0_.location_longitude AS location_longitude_13 FROM barcode_product_result b0_ WHERE b0_.GSIN = ? AND b0_.isNew = ? AND b0_.priceObj_amount >= ? ORDER BY priceObj_amount_10 ASC

Actual:

SELECT b0_.id AS id_0, b0_.feedProvider AS feedProvider_1, b0_.isNew AS isNew_2, b0_.title AS title_3, b0_.description AS description_4, b0_.GSIN AS GSIN_5, b0_.createdAt AS createdAt_6, b0_.url AS url_7, b0_.imageUrl AS imageUrl_8, b0_.fpExtendedAttributes AS fpExtendedAttributes_9, b0_.priceObj_amount AS priceObj_amount_10, b0_.priceObj_currencyCode AS priceObj_currencyCode_11, b0_.location_latitude AS location_latitude_12, b0_.location_longitude AS location_longitude_13 FROM barcode_product_result b0_ WHERE b0_.GSIN = ? AND b0_.isNew = ? AND b0_.priceObj_amount >= ? ORDER BY b0_.priceObj_amount ASC

The key difference is that "b0.priceObj_amount" should become "priceObj_amount_10" according to the "OriginalFieldName" As "Re-mappedName"

@jackdpeterson commented on GitHub (Aug 5, 2016): Expected: ``` SELECT b0_.id AS id_0, b0_.feedProvider AS feedProvider_1, b0_.isNew AS isNew_2, b0_.title AS title_3, b0_.description AS description_4, b0_.GSIN AS GSIN_5, b0_.createdAt AS createdAt_6, b0_.url AS url_7, b0_.imageUrl AS imageUrl_8, b0_.fpExtendedAttributes AS fpExtendedAttributes_9, b0_.priceObj_amount AS priceObj_amount_10, b0_.priceObj_currencyCode AS priceObj_currencyCode_11, b0_.location_latitude AS location_latitude_12, b0_.location_longitude AS location_longitude_13 FROM barcode_product_result b0_ WHERE b0_.GSIN = ? AND b0_.isNew = ? AND b0_.priceObj_amount >= ? ORDER BY priceObj_amount_10 ASC ``` Actual: ``` SELECT b0_.id AS id_0, b0_.feedProvider AS feedProvider_1, b0_.isNew AS isNew_2, b0_.title AS title_3, b0_.description AS description_4, b0_.GSIN AS GSIN_5, b0_.createdAt AS createdAt_6, b0_.url AS url_7, b0_.imageUrl AS imageUrl_8, b0_.fpExtendedAttributes AS fpExtendedAttributes_9, b0_.priceObj_amount AS priceObj_amount_10, b0_.priceObj_currencyCode AS priceObj_currencyCode_11, b0_.location_latitude AS location_latitude_12, b0_.location_longitude AS location_longitude_13 FROM barcode_product_result b0_ WHERE b0_.GSIN = ? AND b0_.isNew = ? AND b0_.priceObj_amount >= ? ORDER BY b0_.priceObj_amount ASC ``` The key difference is that "b0.priceObj_amount" should become "priceObj_amount_10" according to the "OriginalFieldName" As "Re-mappedName"
admin commented 2026-01-22 15:01:41 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Aug 5, 2016):

As mentioned on IRC, might be worth looking into 31a0c02b06/lib/Doctrine/ORM/Query/SqlWalker.php (L330) and code around it, which is responsible for alias generation.

@Ocramius commented on GitHub (Aug 5, 2016): As mentioned on IRC, might be worth looking into https://github.com/doctrine/doctrine2/blob/31a0c02b066764ec7b55eedc5f3cab78235a4e4b/lib/Doctrine/ORM/Query/SqlWalker.php#L330 and code around it, which is responsible for alias generation.
admin commented 2026-01-22 15:01:41 +01:00
Author
Owner
Copy Link

@jackdpeterson commented on GitHub (Aug 5, 2016):

This seems to be a duplicate / slightly different version of the same issues described in #5622. After modifying my entity to not use a value object and just do simple sorting ... this issue (albeit outside of an embeddable) remains.

@jackdpeterson commented on GitHub (Aug 5, 2016): This seems to be a duplicate / slightly different version of the same issues described in #5622. After modifying my entity to not use a value object and just do simple sorting ... this issue (albeit outside of an embeddable) remains.
admin commented 2026-01-22 15:01:43 +01:00
Author
Owner
Copy Link

@jackdpeterson commented on GitHub (Aug 5, 2016):

I can confirm that setting

[mysqld]
sql-mode= "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Does resolve this issue.

@jackdpeterson commented on GitHub (Aug 5, 2016): I can confirm that setting ``` [mysqld] sql-mode= "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" ``` Does resolve this issue.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
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 Bug Missing Tests
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
admin
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5210
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?