doctrine QueryBuilder arithmetic operation

0
votes

I am trying to do an arithmetic operation with doctrine Query Builder and assign the result to an alias. Following is an example from geeksengine that I'd like to "convert" to doctrine QueryBuilder: 

/*
Query 1: This query calculates the total price before  discount and after discount for each order item.
*/
SELECT order_id,
   product_id, 
   unit_price * quantity AS "regular_price", 
FROM order_details;

This is what I started with: 

$queryBuilder = $this->em->createQueryBuilder();
$queryBuilder->select('o')
             ->addSelect('o.unit_price * o.quantity AS regular_price')
             ->from('Test\Model\Entity\OrderEntity', 'o');

But this throws an exception:

RuntimeException
Not all identifier properties can be found in the ResultSetMapping: id

I guess, it's because regular_price is not part of the database - so it can't be mapped.

I can get it done with pure DQL but for some reasons I have to stick with the QueryBuilder.

Any ideas?

1
phpdoctrine-ormdoctrine-query

1 Answers

0
votes

You can use the arithmetic Expr function prod (doc), then assign the result to your alias:

$queryBuilder = $this->em->createQueryBuilder();
$queryBuilder->select('o')
             ->addSelect($queryBuilder->expr()->prod('o.unit_price', 'o.quantity').' AS regular_price')
             ->from('Test\Model\Entity\OrderEntity', 'o');