CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-orm
Rodgard
Rodgard

Reputation: 119

Order by calculated fields in Doctrine QueryBuilder

I have the following fields in one of my entities: price(decimal), promo(boolean), and promoPrice(decimal) and I want to get query with order by real price, so I need to create query like this one:

SELECT *, (!promo*price + promo*promo_price) as real_price 
FROM `product` ORDER BY real_price ASC

Is there any way to do it with QueryBuilder or maybe I need to use some native methods?

Upvotes: 1

Views: 2717

Answers (1)

Rodgard
Rodgard

Reputation: 119

SOLUTION:

$repository = $this->getDoctrine()->getRepository('AppBundle:Product');
$query = $repository->createQueryBuilder('p')
    ->addSelect('CASE WHEN p.promo = :value THEN p.promoPrice ELSE p.price END AS HIDDEN realPrice')
    ->setParameter('value', true)
    ->orderBy('realPrice', 'ASC')
    ->getQuery();

Upvotes: 4

Related Questions