Where statements are not being appended

Question

I am trying to build a query dynamically. It's initial state is fine. This is the initial where clause that should be present on every query

$qb->add('where', $qb->expr()->andx(
    $qb->expr()->eq('s.competitor', $competitor),
    $qb->expr()->eq('s.ignored', $ignored),
    $qb->expr()->eq('s.id', $params['s_id']),
    $qb->expr()->eq('s.id', 'k.targetSite')
), true);

But the app I am building allows users to filter. When that happens, I want to add additional where clauses into my query builder. When this line is executed later in the code, it overwrites the above where statement.

$qb->add('where',  $qb->expr()->like($col, $val), true );

From what I have read, the 3rd parameter $append should keep the previous statements, but that's not happening. In Doctrine 1.2, I could just do something like this:

foreach($filter as $col => $val) {
    $dql->addWhere($col = ?, array($val));
}

How do I dynamically add where clauses to my QueryBuilder?

Update

Here is a full statement

$where = array('col' => 'k.text', 'val' => 'some word%');

$qb = $this->entityManager->createQueryBuilder()
    ->select('s, sc')
    ->from('Dashboard\Entity\Section', 'sc')
    ->innerJoin('sc.keyword', 'k')
    ->innerJoin('sc.site', 's')
    ->leftJoin('k.keywordCategory', 'kc')
    ->leftJoin('k.keywordSubCategory', 'ksc');

$qb->add('where', $qb->expr()->andx(
    $qb->expr()->eq('s.competitor', $competitor),
    $qb->expr()->eq('s.ignored', $ignored),
    $qb->expr()->eq('s.id', $params['s_id']),
    $qb->expr()->eq('s.id', 'k.targetSite')
), true);

if ($where) {
    $qb->add('where', $qb->expr()->andx(
             $qb->expr()->like($where['col'], $where['val'])
    ), true);
}

$qb->addGroupBy('k.id');
    $qb->addGroupBy('s.id');

$qb->setFirstResult( $params['start'] )
   ->setMaxResults( $params['limit'] );

$q = $qb->getQuery();
echo $q->getSql();

And the output is

SELECT s0_.id AS id0, k1_.id AS id1, k1_.name AS name2, k2_.id AS id3, k2_.name AS   name4, k3_.id AS id5, k3_.text AS text6, k3_.search_vol AS search_vol7, s4_.id AS id8, s4_.sub_domain AS sub_domain9, MIN(s0_.rank) AS sclr10, MAX(s0_.created) AS sclr11 
FROM section s0_ 
INNER JOIN keyword k3_ ON s0_.k_id = k3_.id 
INNER JOIN site s4_ ON s0_.s_id = s4_.id 
LEFT JOIN keyword_category k1_ ON k3_.k_cat_id = k1_.id 
LEFT JOIN keyword_sub_category k2_ ON k3_.k_subcat_id = k2_.id 
WHERE k3_.text LIKE 'some word%' 
GROUP BY k3_.id, s4_.id LIMIT 25 OFFSET 0

If I don't add in that if ($where) clause, then the first andx where statements are still in place. But when I try to dynamically add them, only the final WHERE statement is added, all others are cleared. I should also add, that I tried it like this as well.

if ($where) {
    $qb->add('where', $qb->expr()->like($where['col'], $where['val']), true);
}

I can successfully use

$qb->andWhere( $qb->expr()->like($where['col'], $where['val']) );

But the API docs for Query Builder state the way I am trying to use it should be valid too. Wanted to make sure I was doing it right, or if it was a bug.

Answer 1

You're able to use the ->andWhere() normally (and it will also fixes your issue).

Doctrine 2 QueryBuilder is a rather innovative concept (because it mixes both programmatic and fluent styles), and there are likely bugs associated to it.

One point that you should notice in your code: Instead of play with ->add('where', ...), you should think programmatic. Add more items to andX() object and at the end associate to the ->add('where', ...) (or even better: ->where(...))