How to build a query for a filter with complex conditions in the doctrine?

Question

Doctrine ORM

I'm filtering a directory. It has a lot of options for filtering, a simplified line looks something like this

/[email protected]=1&[email protected]=2015|2019&[email protected]=>&[email protected]=красный|синий&[email protected]=bmw|honda

I came up with an algorithm for this string, maybe it’s not very good of course - what I thought of, I tried to look for other options, only elasticsearch came up with parameters, but it’s redundant for my project,

I made a Parameter base class and extended the base class with specific types in which the request is built
of type Simple , Bool , Range (plus sorting and value for paging),

each type builds a query itself, for example, this is a method of Range type

public function buildQuery(QueryBuilder $qb): QueryBuilder
    {
        [$normalizedKey] = $this->getKeys($qb); //значение вместе с алиасом

        if ($this->getFrom()) {
            $qb->andWhere($qb->expr()->andX(
                $qb->expr()->gte($normalizedKey, $this->getFrom())
            ));
        }

        if ($this->getTo()) {
            $qb->andWhere($qb->expr()->andX(
                $qb->expr()->lte($normalizedKey, $this->getTo())
            ));
        }

        return $qb;
    }

everything works fine, but the problem is that there are MANDATORY parameters (the same Range ) - that is, the AND
condition must be met . But the Array type has a different condition, for example [email protected]=red|blue, that is, in the results with mandatory parameters, you need to find some from the set, or red or blue I built such a query and it works

public function buildQuery(QueryBuilder $qb): QueryBuilder
    {
        [$normalizedKey, $key] = $this->getKeys($qb);
        $values = $this->getValue();

        $qb->andWhere($normalizedKey . ' IN (:values)')->setParameter('values', $values, Connection::PARAM_STR_ARRAY);

        return $qb;
    }

BUT, if there are several such filters, everything collapses, the condition does not fit, I don’t understand why, I’ve already broken my head,
there is an option to filter already at the front using js

Please tell me if there are any options
, just in case I’ll attach a DQL dump

"SELECT c FROM App\Entity\Car c WHERE c.isStock = :isStock AND c.year >= 2015 AND c.year <= 2019 AND c.color IN (:values) AND c.brand IN (:values)"

Answer 1

In a loop for different filters, you add parameters with one key to one QueryBuilder object values, then when assembling the query, Doctrine gets the first Parameter found with a key values(and there are several of them, but the first one is enough for it).
Doctrine code:

That is, for color, year, level - the first parameter with the key is always taken. I values
suggest to substitute a different alias instead of this word, for example key:

[$normalizedKey, $key] = $this->getKeys($qb);

$qb
  ->andWhere(sprintf('%s IN (:%s)', $normalizedKey, $key))
  ->setParameter($key, $this->getValue());