Search code examples
mysqlsymfonydoctrine-ormdql

Adding If condition to where clause in DQL / Symfony 3

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?

Solution

  • 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.