如何使用Doctrine绑定动态值

Question

I have an array of parameters wich I use to bind values in my query.

$params = [
            'brandFilter' => $articles['brands'],
            'categoryFilter' => $articles['categories'],
            'priceFromFilter' => $articles['prices']['from'],
            'priceToFilter' => $articles['prices']['to'],
        ];

My problem is that sometimes one or several of these parameters can be empty because it depends on what the user has checked.

I can do my queries using if !empty but it quickly becomes ugly to write and read, as following :

$qb = $this->createQueryBuilder('a');
        $qb->select('a');

if (!empty($articles['brands']) && !empty($articles['categories']) && !empty($articles['prices']['from']) && !empty($articles['prices']['to'])) {

    $qb->where('a.brand IN (:brandFilter)')
       ->andWhere('a.category IN (:categoryFilter)')
       ->andWhere('a.price BETWEEN :priceFromFilter AND :priceToFilter')
       ->setParameters($params);
}

elseif (!empty($articles['brands']) && !empty($articles['categories'])) {
   $this->findByBrandsAndCategories($qb, $articles);

} elseif (!empty($articles['brands']) && empty($articles['categories'])) {
   $this->findByBrands($qb, $articles);

} elseif (!empty($articles['categories']) && empty($articles['brands'])) {
   $this->findByCategories($qb, $articles);
}

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

It's really long to write and I was wondering if anyone had another solution than going through condition blocks ?

Answer 1

I think generating the query as follows could suit your needs:

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

if (!empty($articles['brands'])) {
    $qb
        ->andWhere('a.brand IN (:brandFilter)')
        ->setParameter('brandFilter', $articles['brands']);
}

if (!empty($articles['categories'])) {
    $qb
        ->andWhere('a.category IN (:categoryFilter)')
        ->setParameter('categoryFilter', $articles['categories']);
}

if (!empty($articles['prices']['from'])) {
    $qb
        ->andWhere('a.price >= (:priceFromFilter)')
        ->setParameter('priceFromFilter', $articles['prices']['from']);
}

if (!empty($articles['prices']['to'])) {
    $qb
        ->andWhere('a.price <= (:priceToFilter)')
        ->setParameter('priceToFilter', $articles['prices']['to']);
}