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

[jackdpeterson]

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

[jackdpeterson]

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"

[Ocramius]

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.

[jackdpeterson]

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]

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.