How to use andWhere and orWhere in Doctrine?

Constructing complex conditions

You're a pro. You're wrangling complex conditions. But wait? How do you make andWhere and orWhere play well together? Ah, yes. You guessed it. The Expr class to the rescue!

Getting expressive with nested conditions

To stack conditions, choosing ->expr()->andX() and ->expr()->orX() methods are your best shot. They will help you mesh conditions and make subqueries. Like love and marriage, they go together like a horse and carriage.

$queryBuilder->where(
  $queryBuilder->expr()->andX(
    $queryBuilder->expr()->eq('alias.column1', ':value1'),
    $queryBuilder->expr()->orX(
      $queryBuilder->expr()->eq('alias.column2', ':value2'),
      $queryBuilder->expr()->like('alias.column3', ':value3')
    )
  )
);


Dynamically dynamics conditions

Sometimes you need to add conditions on the fly. We are living in a dynamic world after all. Just create an array of conditions and run a loop over them. It's like making a shopping list for your query!

$conditions = [...];  // Like groceries, you need predicates.
$composite = $queryBuilder->expr()->orX();

foreach ($conditions as $condition) {
  $composite->add($condition); // Adding to the cart
}

$queryBuilder->where($composite); // Ready for checkout


Literal values, comparisons and the rest

Stick to ->expr()->eq() for equals, ->expr()->like() for your LIKE conditions, and ->expr()->literal() for literal values. They give your query the zest it needs!

$queryBuilder->where(
  $queryBuilder->expr()->eq('alias.column', $queryBuilder->expr()->literal('SomeValue'))
);


Scaling up performance and avoiding pitfalls

Every query is a maze, and to make it through, here are some quick boosters and traps to avoid in Doctrine:

No room for redundant conditions

Redundant conditions are like pineapple on your pizza when you asked for only pepperoni – too much can spoil the recipe.

Proper use of parameters

Always use ? (placeholders) or :named parameters as a shield for SQL injection and to cook up more efficient queries.

Mind your response format

Chewing on the right Fetch mode can have a big impact on performance. If fully loaded objects are overkill, opt for a lighter array format with getArrayResult.

Some useful tips and tricks

A couple extra tricks to whip up robust and easily digestible queries:

Taking a leaf out of Propel’s book

Propel's getNewCriterion function can give you ideas on how to nest conditions effectively in Doctrine. Find your inspiration!

The Doctrine's word

Deepen your knowledge by regularly sleeping with the Doctrine documentation under your pillow. As rich as a pan pizza, it's full of examples and best practices.

Championing multiple conditions

Cook up complex conditions like a pro with arrays and loops. Your queries will be leaner, meaner and ready to serve up results.