Symfony2/Doctrine QueryBuilder using andwhere()

Question

I am using following method in a repository class to look for certain tags in my database:

public function getItemsByTag($tag, $limit = null)
{
    $tag = '%'.$tag.'%';

    $qb = $this->createQueryBuilder('c');

    $qb->select('c')
       ->where($qb->expr()->like('c.tags', '?1'))
       ->setParameter(1, $tag)
       ->addOrderBy('c.clicks', 'DESC');

    if (false === is_null($limit))
        $qb->setMaxResults($limit);

    return $qb->getQuery()->getResult();
}

This works just nice.. But: How can I add 2 additional variables (where: reviewed = 1, enabled = 1)? I tried andwhere() but I couldn't figure it out.

I also found out that something like this:

public function getItems($limit = null)
{
        $qb = $this->createQueryBuilder('b')
               ->select('b')
               ->add('where', 'b.reviewed = 1')
               ->add('where', 'b.enabled = 1')
               ->addOrderBy('b.name', 'ASC');

        // ...
}

won't work either...

Any hints?

Elnur Abdurrakhimov · Accepted Answer · 2012-01-16 07:31:46Z

31

I would write it like this:

$qb = $this
    ->createQueryBuilder('c')
    ->where('c.tags LIKE :tag')
    ->andWhere('c.reviewed = 1')
    ->andWhere('c.enabled = 1')
    ->setParameter('tag', "%{$tag}%")
    ->orderBy('c.clicks', 'DESC')
    ->addOrderBy('b.name', 'ASC');

if ($limit) {
    $qb->setMaxResults($limit);
}

return $qb->getQuery()->getResult();

You could also unite those where conditions:

->where('c.tags LIKE :tag AND c.reviewed = 1 AND c.enabled = 1')

answered Jan 16, 2012 at 7:31

Elnur Abdurrakhimov

44.9k11 gold badges152 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Carey Estes Over a year ago

I believe addOrderBy is used is you are concatenating logic to the orderBy. For instance if you wanted 2 orderBy parameters, the first would be 'orderBy', followed by 'addOrderBy'. Works the same as the where clause. The first is ->where, then all following are written '->addWhere'.

Niklas · Accepted Answer · 2013-09-26 14:26:31Z

6

From the manual, the suggested way is like below:

$qb->select(array('c'))
   ->where($qb->expr()->orx(
       $qb->expr()->eq('c.reviewed', 1),
       $qb->expr()->eq('c.enabled', 1),
       $qb->expr()->like('c.tags', '?1')
   ))
   ->orderBy('c.clicks', 'DESC'));

edited Sep 26, 2013 at 14:26

Niklas

13.2k24 gold badges82 silver badges124 bronze badges

answered Jan 16, 2012 at 2:21

xdazz

161k38 gold badges255 silver badges278 bronze badges

1 Comment

Mike Over a year ago

Ok Thanks xdazz +1 for that too!

Collectives™ on Stack Overflow

Symfony2/Doctrine QueryBuilder using andwhere()

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related