如果提供条件，则Doctrine为createQueryBuilder添加条件

问题描述：

I have following function:

public function latestNews($tags = array(), $categories = array(), $authors = array(), $lang = 'en', $source = '', $limit = 20) {
    return $this->createQueryBuilder('News')
    ->field('tags')->in($tags)
    ->field('categories')->in($category)
    ->field('authors')->in($authors)
    ->field('lang')->equals($lang)
    ->sort('date' -> 'DESC')
    ->field('source')->equals($source)
    ->limit($limit)
    ->getQuery()
    ->execute();
}

I want if variables such as $tags, $categories, $authors or $source provided by function caller this variables affect on the createQueryBuilder, but if each of them does not provide by the function caller(variable with default value) they don't affect createQueryBuilder and make this condition neutral on query. One way is I make the query with many if condition but it is very messy. Is there any better solution?

我有以下功能： p>

  public function latestNews（$  tags = array（），$ categories = array（），$ authors = array（），$ lang ='en'，$ source =''，$ limit = 20）{
 return $ this-＆gt; createQueryBuilder（' 新闻'）
  - ＆gt;字段（'tags'） - ＆gt; in（$ tags）
  - ＆gt;字段（'categories'） - ＆gt; in（$ category）
  - ＆gt; field（'authors  '） - ＆gt; in（$ authors）
  - ＆gt; field（'lang'） - ＆gt; equals（$ lang）
  - ＆gt; sort（'date' - ＆gt;'DESC'）
  - ＆gt  ; field（'source'） - ＆gt; equals（$ source）
  - ＆gt; limit（$ limit）
  - ＆gt; getQuery（）
  - ＆gt; execute（）; 
} 
  code  >  pre> 
 
 我想要的是 $ tags  code>， $ categories  code>， $ authors  code>或 $ source  code>这些变量影响 createQueryBuilder  code>，但如果它们中的每一个都没有由函数调用者提供（变量具有默认值），则它们不会影响createQueryBuilder和 在查询时使这个条件保持中立。
One 方式是我用许多if条件进行查询，但它非常混乱。
有没有更好的解决方案？ p> 
  div>

答

Something like this should do the trick:

public function latestNews($tags = array(), $categories = array(), authors = array(), $lang = 'en', $source = '', $limit = 20) {
    $inClauses = ['tags', 'categories', 'authors'];
    $equalClauses = ['lang', 'source'];
    $qb = $this->createQueryBuilder('News');

    foreach ($inClauses as $field) {
        $realVar = ${$field};

        if (!empty($realVar)) {
            $qb->field($field)->in($realVar);
        }
    }

    foreach ($equalClauses as $field) {
        $realVar = ${$field};

        if ($realVar) {
            $qb->field($field)->equals($realVar);
        }
    }

    return $qb
        ->sort('date' -> 'DESC')
        ->limit($limit)
        ->getQuery()
        ->execute();
}

A bit ugly, but I don't see any better alternative.

答

The chalasr's response is good, but I suggest extract the logic of build conditions to a specific trait, so you avoid code duplication (DRY).

You can do something like:

<?php

namespace Xthiago\My\Path;

use \Doctrine\DBAL\Query\QueryBuilder;

trait DoctrineQueryHelper
{
    public function in(QueryBuilder $qb, array $filter, $field)
    {
        if (empty($filter[$field])) {
            return $this;
        }

        $qb->field($field)->in($filter[$field]);

        return $this;
    }

    public function equals(QueryBuilder $qb, array $filter, $field)
    {
        if (empty($filter[$field])) {
            return $this;
        }

        $qb->field($field)->equals($filter[$field]);

        return $this;
    }

    public function lang(QueryBuilder $qb, $value = 'en')
    {
        $qb->field('lang')->equals($value);

        return $this;
    }

    public function limit(QueryBuilder $qb, $value = 20)
    {
        $qb->limit($value);

        return $this;
    }

    // you can create a lot of helper methods here in order to avoid duplicity.
}

Then your class make use of the trait in this way:

<?php

namespace Xthiago\My\Path;

use \Doctrine\DBAL\Query\QueryBuilder;

class NewsRepository
{
    use DoctrineQueryHelper;

    /**
     * @param array $filter as follow:
     * <code>
     * [
     *  'tags' => [],
     *  'categories' => [],
     *  'authors' => [],
     *  'lang' => 'en',
     *  'source' => '',
     *  'limit' => 20,
     * ]
     * </code>
     *
     * @return array with results.
     */
    public function latestNews(array $filter = []) 
    {
        $qb = $this->createQueryBuilder('News');

        $this->in($qb, $filter, 'tags')
             ->in($qb, $filter, 'categories')
             ->in($qb, $filter, 'authors')
             ->equals($qb, $filter, 'source')
             ->lang($qb, $filter)
             ->limit($qb, $filter);

        return $qb->sort('date', 'DESC')
                  ->getQuery()
                  ->execute();
    }
}

如果提供条件，则Doctrine为createQueryBuilder添加条件

相关推荐