Geek asks and answers

If statements are not attached

I am trying to build a query dynamically. This initial state is fine. This is the original where clause that should be present in every request

$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 application that I create allows users to filter. When this happens, I want to add additional where clauses to my query builder. When this line is executed later in the code, it overwrites the above statement.

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

From what I read, the third parameter $appendshould contain the previous statements, but this does not happen. In Doctrine 1.2, I could just do something like this:

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

How can I dynamically add where clauses to my QueryBuilder?

Update

Here is the full operator

$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 conclusion

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 ($where), andx . , WHERE, . , .

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


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

API- API Query Builder, , . , , .

+3
2

→ andWhere() ( ).

Doctrine 2 QueryBuilder - ( , ), , , .

, : , → add ('where',...), . andX() → add ('where',...) ( : → (...))

+3

, , , , , , .

add() DQL, , , - , :

   $isMultiple = is_array($this->_dqlParts[$dqlPartName]);
   ...

    if ($append && $isMultiple) {
        if (is_array($dqlPart)) {
            $key = key($dqlPart);

            $this->_dqlParts[$dqlPartName][$key][] = $dqlPart[$key];
        } else {
            $this->_dqlParts[$dqlPartName][] = $dqlPart;
        }
    } else {
        $this->_dqlParts[$dqlPartName] = ($isMultiple) ? array($dqlPart) : $dqlPart;
    }

WHERE, DQL, :

private $_dqlParts = array(
    'select'  => array(),
    'from'    => array(),
    'join'    => array(),
    'set'     => array(),
    'where'   => null,
    'groupBy' => array(),
    'having'  => null,
    'orderBy' => array()
);

, Doctrine 2 , , , . , . , ...

+3

More articles:


All Articles