How to Use Andwhere and Orwhere in Doctrine

How to use andWhere and orWhere in Doctrine?

$q->where("a = 1")
  ->andWhere("b = 1 OR b = 2")
  ->andWhere("c = 2 OR c = 2")
  ;

How to use andWhere and orWhere together in a doctrine query builder

Similar to this

$qb = $this->getEntityManager()->createQueryBuilder();

$qb->select('COUNT(b.id)')
    ->from('AppBundle:Booking', 'b')
    ->where($qb->expr()->eq('b.bookingDate', $date))
    ->andWhere($qb->expr()->between('b.endTime ', $start, $end))
    ->andWhere($qb->expr()->orX(
        $qb->expr()->eq('b.startTime', $start),
        $qb->expr()->between('b.endTime ', $start, $end)
    ));

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

Symfony - using orWhere() in doctrine query builder

The logic of the where statement should work as expected.
But it seems like you are using incorrect parameter binding.

toUser and fromUser are columns and therefore no need to bind them.

$user is the target user that we want to filter on, thus it should be bound to the query.

An example:

{
    return $this->getMessageRepository()
        ->createQueryBuilder('a')
        ->where('a.message LIKE :message')
        ->andWhere("a.toUser = :user OR a.fromUser = :user")
        ->setParameter('message', '%' . $word. '%')
        ->setParameter('user', $user)
        ->getQuery()
        ->getResult();
}

How to group more andWhere, orWhere in Doctrine

For grouping and hierarchy of or/and and the like, you can link and's and or's using querybuilder's ->expr() method chaining onto ->andX() and ->orX() respectively on your instance of QueryBuilder. You can check here for more information: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/query-builder.html#the-expr-class

Basically you'll get something like the following to translate your second statement:

// In this example I'll be assuming that 'dg' is an entity
// and that 'a', 'b' and 'c' are its attributes
// since, remember, Doctrine is designed specifically for using entities
// and make abstraction of the whole table model in your database

// First we'll create your QueryBuilder instance $qb
$qb = $this->createQueryBuilder('dg');

// Then we add our statements to the QueryBuilder instance
$qb
    ->where($qb->eq('dg.a', 1))
    ->andWhere($qb->expr()->orX(
        $qb->expr()->gt('dg.a', 1),
        $qb->expr()->eq('dg.b', 2)
    ))
    ->andWhere($qb->expr()->orX(
        $qb->expr()->gt('dg.a', 1),
        $qb->expr()->eq('dg.c', 3)
    ))
;

// Now you can use the QueryBuilder instance to, for instance, 
// have it do getResult (which in this case will return an array of 'dg' entities)
return $qb->getQuery()->getResult();

You can put orX()'s and andX()'s into other orX()'s and andX()'s as well, and you can add an arbitrary number of conditions in your andX() and orX(), to create very complex queryies.

Have fun :)

How to use orWhere inside andWhere in a doctrine query builder

WHERE (`city_id` = 4 OR `city_id` = 5) AND ... can be rewritten to WHERE `city_id` IN (4, 5) AND ... That will greatly simplify your query builder and provide the same result.

You can now use :

if ($search->getCity()->count() > 0) {
    $citiesId = [];
    foreach ($search->getCity() as $city) {
        $citiesId[] = $city; // get the cities in an array
    }

    // no need to write $query = $query->...
    $query->andWhere("company.city IN (:cities)") // use IN clause
          ->setParameter(":cities", $citiesId);   // and bind the array as parameter
}

You can now apply the same logic for option and other stuffs

---

I don't know what $search->getCity() looks like, but I have the feeling you can just use

if ($search->getCity()->count() > 0) {
    $query->andWhere("company.city IN (:cities)")
          ->setParameter(":cities", $search->getCity());
}

Doctrine - or where?

$query = Doctrine_Query::create()
  ->from('Member m')
  ->where('m.type = 1 AND m.name LIKE ?', '%'.$term.'%')
  ->orWhere('m.type = 1 AND m.surname LIKE ?', '%'.$term.'%')
  ->orWhere('m.type = 1 AND m.company LIKE ?', '%'.$term.'%')
  ->orderBy('m.id DESC');

Your OR conditions didn't include the first condition. It's also recommended to use the ? for your variables to ensure Doctrine escapes them.

Is there a correct way in Doctrine to nest 'OR' where clauses?

As stated in the official documentation for QueryBuilder best practice - the right way is to use the helper method QueryBuilder::expr() and also the Doctrine\ORM\Query\Expr helper class. High level API methods

i.e.:

// create QueryBuilder
$qb = $this->createQueryBuilder('j');

// build conditions
$qb->where(
    $qb->expr()->orX(
        $qb->expr()->eq('j.completed', ':false'),
        $qb->expr()->isNull('j.completed')
    )
)
->andWhere(
    $qb->expr()->eq('j.has_due_date', ':true')
)
->andWhere(
    $qb->expr()->eq('j.has_due_date', ':true')
)
->setParameters(array(
    'false' => FALSE,
    'true' => TRUE
))
->orderBy('j.due_date', 'asc');

// get query
$query = $qb->getQuery();

// execute and get result
$result = $query->getResult();

Doctrine subquery andwhere

For such cases, you can rely on the Expr class which contains the methods you need to build those more complex queries. Everything you need to know is explained in the Doctrine documentation in the Query Builder section.

The current documentation actually gives an example which almost fits with the use case you mentioned. You could write something like this for example:

$qb->andWhere($qb->expr()->orX(
   $qb->expr()->eq('u.id', '?1'),
   $qb->expr()->like('u.nickname', '?2')
));

The main advantage of relying on the Expr class is that you can pretty much write any kind of combination with it and it will produce the right DQL for it. It can get pretty verbose however.

That being said, you can also directly write your OR condition in the andWhere:

$qb->andWhere('u.id = ?1 OR u.nickname = ?2');

---

Related Topics