Dql query with optional parameters

Question

I would like to know how to make a DQL query with optional parameters for exemple:

public function getUsers($city, $sex, $age)
    {
        $qb = $this->getEntityManager()->createQueryBuilder()
            ->select('u')
            ->where('u.sex = :sex')
            ->andWhere('u.city = :city')
            ->andWhere('u.age = :age')
        ->setParameter(':city', $city);
        ->setParameter(':sex', $sex);
        ->setParameter(':age', $age);
        $query = $qb->getQuery();
        $result = $query->getResult();

    }

How to do if one of the parameters is not defined(=NULL) ?

score 1 · Answer 1 · edited May 23 '17 at 11:46

1

In this case, a parameter setted to NULL could have sense, because, even if it is not standard, you can express IS NULL condition with (just for the sake of example) u.age = :age where age is NULL (I suggest to read this topic however)

So, it's pretty clear that you need to check it yourself (if you intend them as optional) and don't add condition (and so parameter bind) if that parameter is NULL.

Pay attention that if sex (that's listed as first parameter) is null, you should use where against andWhere in second where condition and so on.

edited May 23 '17 at 11:46

Community

1
1

answered Oct 26 '16 at 10:41

DonCallisto

29,419
9
72
100

Thanks for this explanation, So what code do you suggest ? – user6533866 Oct 26 '16 at 11:01
You can use only `andWhere` conditions: `$qb = ... ; if(!is_null($myParam)) $qb->andWhere('param = :myParam')->setParameter('myParam', $myParam); $query = ...` – lolmx Oct 26 '16 at 12:04

score 1 · Accepted Answer · edited Oct 26 '16 at 16:51

1

public function getUsers($city, $sex, $age)
{
    $qb = $this->getEntityManager()->createQueryBuilder()
        ->select('u')
        ->from('User', 'u');

    if (isset($sex)) {
        $qb->andWhere('u.sex = :sex')
           ->setParameter(':sex', $sex);
    }

    if (isset($city)) {
        $qb->andWhere('u.city = :city')
           ->setParameter(':city', $city);
    }

    if (isset($sex)) {
        $qb->andWhere('u.sex = :sex')
           ->setParameter(':age', $age);
    }

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

edited Oct 26 '16 at 16:51

yceruto

9,230
5
38
65

answered Oct 26 '16 at 12:06

Saish Sali

274
1
6

Works well when I replace all 'where' by 'Andwhere' – user6533866 Oct 26 '16 at 13:39
My bad. Thanks @user6533866 – Saish Sali Oct 28 '16 at 11:22
Haven't you just reinvented ‘findBy‘? – Stephan Vierkant Jun 08 '20 at 19:00

score 0 · Answer 3 · answered Oct 24 '22 at 12:50

The other comments are correct! However, if you'd like to avoid an if-statement chain you could use the following:

A UserStore containing:

public function search(...UserSearchFilter $filters): array
{
    $alias = 'u';
    $qb = $this->repository->createQueryBuilder($alias);

    foreach ($filters as $filter) {
        $filter->apply($qb, $alias);
    }

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

Somewhere in your code where you need the result:

$this->userStore->search(
    new CityFilter($city),
    new SexFilter($sex)
);

Example filter:

final class CityFilter implements UserSearchFilter
{
    public function __construct(
        private readonly string $city,
    ) {}

    public function apply(QueryBuilder $queryBuilder, string $alias): void
    {
        $queryBuilder
            ->andWhere(\sprintf('%s.city = :city', $alias))
            ->setParameter('city', $this->city);
    }
}

Dql query with optional parameters

3 Answers3