How to combine andWhere and orWhere with Doctrines Criteria

Question

I want to create a rather complex and flexible query with Doctrines Criteria. I have an andWhere that is included in every query, but then (depending on input) I want to add some optinal parameters with orWhere.

$criteria = Criteria::create();

/* Add Criteria for last 55 Days (every query) */
$criteria->where(Criteria::expr()->gte('gameCreation', $long));

/* Gather optional Params */
$orxList = array();

/* Optional Criteria */
if (!empty($champions)) {
    foreach ($champions as $c) {
        $orxList[] = Criteria::expr()->eq('champion',$c);
    }
}

...
$criteria->andWhere(Criteria::expr()->orX($orxList));

This leads to an Exception:

No expression given to CompositeExpression.

How would I combine such a Criteria with the initial where clause?

Possible duplicate of [How to use andWhere and orWhere in Doctrine?](https://stackoverflow.com/questions/9095113/how-to-use-andwhere-and-orwhere-in-doctrine) — RandomSeed, Apr 28 '18 at 08:32
I found that was well, but if the `orWhere` is optional and the query needs to be "extended" I need to create a string query like `$sql .= 'OR champion = ?`. This doesn't seem right? — PrimuS, Apr 28 '18 at 08:52

Fabian Schmick · Answer 1 · 2019-02-14T09:59:16.653

I faced a similar problem and solved it like this:

use Doctrine\Common\Collections\Expr\CompositeExpression;
...

/* Gather optional Params */
$orxList = array();

/* Optional Criteria */
if (!empty($champions)) {
    foreach ($champions as $c) {
        $orxList[] = Criteria::expr()->eq('champion', $c);
    }
}

...
$criteria->andWhere(new CompositeExpression(CompositeExpression::TYPE_OR, $orxList));

As you see the constructor of CompositeExpression accepts an array of expressions and this generates the correct query.

Iwan Wijaya · Answer 2 · 2018-04-28T09:30:40.120

2

You should use orX() from Expr class for this, example:

$orX = $exp->orX();
$index = 0;

foreach ($champions as $c) {
    $paramName = ':param_' . $index;
    $orX->add($qb->expr()->eq('u.column', $paramName));
    $qb->setParameter($paramName, $c);

    $index++;
}

However, if your array are just plain strings/object compared in the same way, consider using WHERE IN [] expression.

edited Apr 28 '18 at 09:30

answered Apr 28 '18 at 08:53

Iwan Wijaya

2,077
1
16
14

this looks promising, but I can't seem to find the right `Expr()` class? – PrimuS Apr 28 '18 at 10:55
Oh, what I meant was the one returned from querybuilder, something like $queryBuilder->expr()->orX(). If you are not using querybuilder I think you can create instance of this class: https://www.doctrine-project.org/api/orm/2.6/Doctrine/ORM/Query/Expr.html For some reason it's not available in Criteria::expr(). – Iwan Wijaya Apr 28 '18 at 13:05
@PrimuS, use one of these in terms of namespaces: use Doctrine\ORM\Query\Expr\Andx; use Doctrine\ORM\Query\Expr\Orx; – roelleor Sep 04 '19 at 07:57

How to combine andWhere and orWhere with Doctrines Criteria

2 Answers2