How to build a dynamic doctrine query?

Question

I am trying to build a dynamic doctrine query. When I try like this, it works

    $qb->andWhere($qb->expr()->orX(
        $qb->expr()->andX(
            $qb->expr()->eq('t.width', '245'),
            $qb->expr()->eq('t.height', '45'),
        ),
        $qb->expr()->andX(
            $qb->expr()->eq('t.width', '225'),
            $qb->expr()->eq('t.height', '65'),
        )
    ));

But I will pass the key and value from the array.

My array looks like this:

[
  0 => [
    "width" => "245"
    "height" => "45"
  ]
  1 => [
    "width" => "225"
    "height" => "65"
  ]
]

Now, I tried following code.

    $conditions = $qb->expr()->orX(
        $qb->expr()->andX()
    );

    foreach ($wheres as $outerKey => $outerValue) {
        foreach ($outerValue as $innerKey => $innerValue) {
            $conditions->add("te.$innerKey = :innerValue");
            $qb->setParameter('innerValue', $innerValue);
        }
    }
    $qb->andWhere($conditions);

    dd($qb->getDQL());

But the SQL returned is not the same as when I tried with static value.

Answer 1

So you can do this in 2 ways (that I could think of). First, using expressions like you showed above but with and extra private function:

private function getExpressions(QueryBuilder $qb, array $fields)
{
    $andX = $qb->expr()->andX();
    foreach ($fields as $field => $value) {
        $andX->add($qb->expr()->eq('t.' . $field, $value));
    }

    return $andX;
}

public function getDQL($conditions)
{
    $qb = $this->createQueryBuilder('t');

    $orX = $qb->expr()->orX();
    foreach ($conditions as $i => $fields) {
        $orX->add($this->getExpressions($qb, $fields));
    }
    $qb->add('where', $orX);

    dump($qb->getDQL());
    exit;
}

For me it was a bit time consuming to figure this out. I actually did it a lot faster the way mentioned above (building the where clause manually):

$i = 0;
foreach ($conditions as $fields) {
    $j = 0;
    foreach ($fields as $field => $value){
        $whereStr .= " " . $field . " = " . $value;
        $j++;
        if ($j < count($fields)){
            $whereStr .= " AND";
        }
    }
    
    $i++;
    if ($i < count($conditions)){
        $whereStr .= " OR";
    }
}

If I understand your logic correctly this should work. You can switch the orX/andX expressions if I misunderstood your requirements.