CODEWITHSUNDEEP
symfonydoctrine
Mohammad Fareed
Mohammad Fareed

Reputation: 1972

DQL and multiple WHERE statements

I want to write query with multiple WHERE statements.

$query = $this->createQueryBuilder('s');

    $query->select('s.id,s.internalId as internal_id,s.firstName as first_name,s.lastName as last_name,st.name as store_name,sts.name as status_name')
        ->leftJoin('s.store', 'st')
       ->leftJoin('s.studentStatus', 'sts')
        ->where($query->expr()->orx(
            //$query->expr()->eq('s.store_id', ':location'),
            $query->expr()->like('s.internalId', ':internalId'),
            $query->expr()->like($query->expr()->concat($query->expr()->literal(' '), 'lower(s.firstName)'), ':name'),
            $query->expr()->like($query->expr()->concat($query->expr()->literal(' '), 'lower(s.lastName)'), ':name'),
            $query->expr()->like($query->expr()->concat($query->expr()->concat($query->expr()->literal(' '), 'lower(s.firstName)'), $query->expr()->concat($query->expr()->literal(' '), 'lower(s.lastName)')), ':name'),
            $query->expr()->like($query->expr()->concat($query->expr()->concat($query->expr()->literal(' '), 'lower(s.lastName)'), $query->expr()->concat($query->expr()->literal(' '), 'lower(s.firstName)')), ':name')
        ))
        ->setParameter('name', "%{$term}%")
        ->setParameter('internalId', "%{$internalId}")
        //->setParameter('location', "{$location}")
        ->orderBy('s.lastName', 'ASC')
        ->setMaxResults((int) $limit);

Commented line in the above query is not working.

Upvotes: 2

Views: 2457

Answers (1)

Grzegorz Gajda
Grzegorz Gajda

Reputation: 2474

DoctrineQueryBuilder (DQL) has those helper methods:

->where($where)
->andWhere($where)
->orWhere($where)

also DQL allows to build expressions:

$qb->add('select', new Expr\Select(array('u')))
   ->add('from', new Expr\From('User', 'u'))
   ->add('where', $qb->expr()->orX(
       $qb->expr()->eq('u.id', '?1'),
       $qb->expr()->like('u.nickname', '?2')
   ))
   ->add('orderBy', new Expr\OrderBy('u.name', 'ASC'));

Using where clear all previous where statements.

Using orWhere place OR for next statements.

Using andWhere place AND for next statements.

You should build query like that:

$query = $this->createQueryBuilder('s')
    ->select('s.id,s.internalId as internal_id,s.firstName as first_name,s.lastName as last_name,st.name as store_name,sts.name as status_name')
    ->leftJoin('s.studentStatus', 'sts')
    ->leftJoin('s.store', 'st')
    ->where('s.store_id = :location') // not working
    ->andWhere('lower(s.firstName) LIKE :name')
    ->orWhere('lower(s.lastName) LIKE :name')
    ->orWhere('s.internalId LIKE :internalId')
    ->setParameter('name', "%{$term}%")
    ->setParameter('internalId', "%{$term}%")
    ->setParameter('location', $location)
    ->orderBy('s.lastName', 'ASC')
    ->setMaxResults((int) $limit)
    ->getQuery();

Upvotes: 3

Related Questions