-1

I've a doctrine entity with two variables $price & $price_new mapped to mysql table with same column name. $price contains legacy values and $price_new contains new values. I want to write a query which checks if $price_new > 0 then apply where on $price_new but if $price_new = 0, $price is used in where clause.

Is there any way to implement it in DQL?

marcell
  • 1,498
  • 1
  • 10
  • 22
Faizan
  • 389
  • 3
  • 10

2 Answers2

0

You could write a DQL using or conditions for your criteria

select a
from YourBundle:Entity a
where (price_new > 0 and price_new = :price_var)
or (price_new = 0 and price = :price_var )

Note the parenthesis around filter criteria are important to satisfy condtions

where price_new is greater 0 then price_new = some price value if this filter returns false then next clause will check if price_new equals 0 then price = some price value

You could use doctrine query builder but make sure you add these parenthesis

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
0

Do you by chance look for something like this?

SELECT p
FROM App\Entity\Product p
WHERE (p.price_new > 0 AND p.price_new = :price)
    OR (p.price_new = 0 AND p.price = :price)

If this is in fact what you are looking for, I would suggest creating a migration that updates your table's price field with the new price and then you can throw out the somewhat redundant price_new. If you need the current state you can create a database dump and retrieve both values, but in your live system you likely only want to have one price.

The migration could look something like this (real SQL this time):

UPDATE product SET product.price = product.price_new WHERE product.price_new > 0;

Then it's a matter of removing the redundant price_new both in your code and in your database and you are good to go.

dbrumann
  • 16,803
  • 2
  • 42
  • 58