ORDER BY not working with aliases #5597

New Issue

2026-01-22T15:12:21+01:00

admin commented

2026-01-22 15:12:21 +01:00

Originally created by @MaximStrutinskiy on GitHub (Jun 30, 2017).

Originally assigned to: @Ocramius on GitHub.

[symfony3, doctrine orm]
Hi everyone.
I create custom date in ->search() for sorting my items by this field, create name for this:
"(certificate.price) - ((certificate.price / 100) * (certificate.discount)) AS total_price",
but i cant use them in ->andWhere(), i write formula again.
Can i use something this name "total_price" without "(certificate.price) - ((certificate.price / 100) * (certificate.discount))"

/**
     * @param null $category_parent
     * @param null $category_children
     * @param null $chose_field
     * @param null $more_than
     * @param null $less_than
     * @return \Doctrine\ORM\Query
     */
    public function findCertificateCatalogQuery(
        $category_parent = null,
        $category_children = null,
        $chose_field = null,
        $more_than = null,
        $less_than = null
    ) {
        $qb = $this->createQueryBuilder('certificate');

        $qb
            ->select(
                'certificate, category, (certificate.price) - ((certificate.price / 100) * (certificate.discount)) AS total_price'
            )
            ->leftJoin('certificate.category', 'category');

        if ($category_parent == 'all' and $category_children == 'all') {
            // Select all.
            $qb
                ->andWhere('category.id >= 0');
        } elseif ($category_parent !== 'all' and $category_children == 'all') {
            // Select parent and children.
            $qb
                ->orWhere('category.id = :parent_category_id')
                ->orWhere('category.parent = :parent_category_id')
                ->setParameter('parent_category_id', $category_parent);
        } else {
            // Select children.
            $qb
                ->andWhere('category.id = :children_category_id')
                ->setParameter('children_category_id', $category_children);
        }

        if ($more_than !== null) {
            $qb
                ->andWhere('(certificate.price) - ((certificate.price / 100) * (certificate.discount)) >= :more_than')
                ->setParameter('more_than', $more_than);
        }

        if ($less_than !== null) {
            $qb
                ->andWhere('(certificate.price) - ((certificate.price / 100) * (certificate.discount)) <= :less_than')
                ->setParameter('less_than', $less_than);
        }

        if ($chose_field !== null) {
            if ($chose_field == 'price') {
                $qb
                    ->addOrderBy('certificate.'.$chose_field, 'ASC')
                    ->addOrderBy('total_'.$chose_field, 'ASC');
            } elseif ($chose_field == 'name') {
                $qb
                    ->addOrderBy('certificate.'.$chose_field, 'ASC');
            }
        }

        // For pagination
        // example: https://stackoverflow.com/questions/30458347/query-builder-and-group-by-on-two-columns-in-symfony2-doctrine-generate-duplic
        //        $qb
        //            ->setMaxResults($pagination)// limit
        //            ->setFirstResult($pagination * $page);

        return $qb->getQuery();
    }

Originally created by @MaximStrutinskiy on GitHub (Jun 30, 2017). Originally assigned to: @Ocramius on GitHub. [symfony3, doctrine orm] Hi everyone. I create custom date in ->search() for sorting my items by this field, create name for this: "(certificate.price) - ((certificate.price / 100) * (certificate.discount)) AS total_price", but i cant use them in ->andWhere(), i write formula again. Can i use something this name "total_price" without "(certificate.price) - ((certificate.price / 100) * (certificate.discount))" ``` /** * @param null $category_parent * @param null $category_children * @param null $chose_field * @param null $more_than * @param null $less_than * @return \Doctrine\ORM\Query */ public function findCertificateCatalogQuery( $category_parent = null, $category_children = null, $chose_field = null, $more_than = null, $less_than = null ) { $qb = $this->createQueryBuilder('certificate'); $qb ->select( 'certificate, category, (certificate.price) - ((certificate.price / 100) * (certificate.discount)) AS total_price' ) ->leftJoin('certificate.category', 'category'); if ($category_parent == 'all' and $category_children == 'all') { // Select all. $qb ->andWhere('category.id >= 0'); } elseif ($category_parent !== 'all' and $category_children == 'all') { // Select parent and children. $qb ->orWhere('category.id = :parent_category_id') ->orWhere('category.parent = :parent_category_id') ->setParameter('parent_category_id', $category_parent); } else { // Select children. $qb ->andWhere('category.id = :children_category_id') ->setParameter('children_category_id', $category_children); } if ($more_than !== null) { $qb ->andWhere('(certificate.price) - ((certificate.price / 100) * (certificate.discount)) >= :more_than') ->setParameter('more_than', $more_than); } if ($less_than !== null) { $qb ->andWhere('(certificate.price) - ((certificate.price / 100) * (certificate.discount)) <= :less_than') ->setParameter('less_than', $less_than); } if ($chose_field !== null) { if ($chose_field == 'price') { $qb ->addOrderBy('certificate.'.$chose_field, 'ASC') ->addOrderBy('total_'.$chose_field, 'ASC'); } elseif ($chose_field == 'name') { $qb ->addOrderBy('certificate.'.$chose_field, 'ASC'); } } // For pagination // example: https://stackoverflow.com/questions/30458347/query-builder-and-group-by-on-two-columns-in-symfony2-doctrine-generate-duplic // $qb // ->setMaxResults($pagination)// limit // ->setFirstResult($pagination * $page); return $qb->getQuery(); } ```

admin added the Invalid Question labels 2026-01-22 15:12:21 +01:00

admin closed this issue

2026-01-22 15:12:22 +01:00

admin commented

2026-01-22 15:12:22 +01:00

@Seb33300 commented on GitHub (Jul 10, 2017):

No, you can't refer to a column alias in the WHERE clause, only in the ORDER BY.

It's a SQL limitation:
https://stackoverflow.com/questions/11182339/reference-alias-calculated-in-select-in-where-clause

@Seb33300 commented on GitHub (Jul 10, 2017): No, you can't refer to a column alias in the WHERE clause, only in the ORDER BY. It's a SQL limitation: https://stackoverflow.com/questions/11182339/reference-alias-calculated-in-select-in-where-clause

admin commented

2026-01-22 15:12:22 +01:00

@lcobucci commented on GitHub (Aug 21, 2017):

@MaximStrutinskiy you should be able to use aliases In HAVING clause.

@lcobucci commented on GitHub (Aug 21, 2017): @MaximStrutinskiy you should be able to use aliases In HAVING clause.

admin commented

2026-01-22 15:12:24 +01:00

@Ocramius commented on GitHub (Aug 21, 2017):

Closing as per @Seb33300's comment.

@Ocramius commented on GitHub (Aug 21, 2017): Closing as per @Seb33300's comment.

admin commented

2026-01-22 15:12:24 +01:00

@yceruto commented on GitHub (Jun 14, 2018):

This issue is confusing, whether the original title as the last one refers to the ORDER BY clause, but this was closed per @Seb33300's comment, which refers to the WHERE clause.

As workaround you can use the field position in SELECT clause (tried in DBAL layer):

$qb
    ->select('o.name', 'SUM(o.value) / COUNT(o.id) * 100 AS `percent`')
    // ...
    ->groupBy(...)
    ->orderBy('2', 'DESC') // order by percent, desc
;

Even though MySQL, SQL Server and probably others, support alias in order by clause.

@yceruto commented on GitHub (Jun 14, 2018): This issue is confusing, whether the original title as the last one refers to the `ORDER BY` clause, but this was closed per @Seb33300's comment, which refers to the `WHERE` clause. As workaround you can use the field position in `SELECT` clause (tried in DBAL layer): ```php $qb ->select('o.name', 'SUM(o.value) / COUNT(o.id) * 100 AS `percent`') // ... ->groupBy(...) ->orderBy('2', 'DESC') // order by percent, desc ; ``` Even though MySQL, SQL Server and probably others, support alias in order by clause.

admin referenced this issue

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

[PR #5597] Fix documentation of schema tool's "save mode" #9662

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#5597

ORDER BY not working with aliases #5597

`ORDER BY` not working with aliases #5597