Filtering by a large number of QueryBuilder parameters, what are the options?

Question

symfony

Hello everyone, we have a "super entity" in the application with which everything is connected - CoreEntity and its CoreEntityRepository, which has a method for getting this entity with a lot of parameters and innerJoin, leftJoin and a lot of if(isset($option['nameOption]) ,
Is there any pattern to get rid of this, or any ideas?

public function findCoreEntity(array $options): array
{
 /** @var EntityRepository $repository */
        $repository = $this->entityManager->getRepository(CoreEntity::class);
        $queryBuilder = $repository->createQueryBuilder('c, 'c.id')
            ->innerJoin('c.firstEntity', 'f')
            ->innerJoin('c.secondEntity', 's')
            ->where('1 = 1');

        if (!empty($options['firstEntityID'])) {
            $queryBuilder->andWhere('f.id = :FIRST_ID')
                ->setParameter(':FIRST_ID', $options['firstEntityID']);
        }

       if (!empty($options['N+1EntityOption'])) {
            $queryBuilder->andWhere('n+1.id  IB (:N+1EntityOption)' )
                ->setParameter(':N+1EntityOption'', $options['N+1EntityOption'']);
        }

      if (PRICE::FILTER_PRICE_TYPE) {
            $queryBuilder
                ->innerJoin('c.prices', 'pp' , 'WITH', 'pp.type = :PRICE_TYPE')
                ->setParameter(':PRICE_TYPE', $type);

      }
}

Answer 1

Did something like Ilya Zyabirov described

interface PropertyQueryOptionsInterface
{

    /**
     * @param array $attribute
     */
    public function fill(array $attribute): void;

    /**
     * @param QueryBuilder $queryBuilder
     */
    public function addWhere(QueryBuilder $queryBuilder): void;
}

class RangePropertyPriceOption implements PropertyQueryOptionsInterface
{

    public const TYPE = 'RangePropertyPriceOption';

    /**
     * @var string
     */
    protected $alias;

    /**
     * @var float
     */
    protected $min = null;

    /**
     * @var float
     */
    protected $max = null;

    /**
     * @param array $attribute
     */
    public function fill(array $attribute): void
    {

        if (isset($attribute['key'])) {
            $this->alias = $attribute['key'];
        }

        if (isset($attribute['value'])) {

            if (isset($attribute['value'][0])) {
                $this->min = $attribute['value'][0];
            }

            if (isset($attribute['value'][1]) && $attribute['value'][1] != 0) {
                $this->max = $attribute['value'][1];
            }
        }

    }

    public function addWhere(QueryBuilder $queryBuilder): void
    {

        $whereByValue = $this->alias === Property::FILTER_RANGE_PRICE ? 'price' : 'areaPrice';

        if (!is_null($this->min)) {

            $whereParameter = ':' . 'min_' . $whereByValue;

            $queryBuilder
                ->andWhere(sprintf('p.%s >= %s', $whereByValue, $whereParameter))
                ->setParameter($whereParameter, $this->min);
        }

        if (!is_null($this->max) && $this->max != 0) {

            $whereParameter = ':' . 'max_' . $whereByValue;

            $queryBuilder
                ->andWhere(sprintf('p.%s <= %s', $whereByValue, $whereParameter))
                ->setParameter($whereParameter, $this->max);
        }
    }
}

foreach ($options['propertyQueryOptions'] as $queryOption) {

                if ($queryOption instanceof PropertyQueryOptionsInterface) {
                    $queryOption->addWhere($queryBuilder);
                }

            }

Answer 2

Doctrine Specification Pattern
or your reusable QueryBuilder
There are also filters (but they are so-so): Doctrine Filters

Answer 3

You can make an interface for filters and implement it for each specific filter. It will have methods for getting an alias (to turn an array of filters from a query into an array of classes), setting a filter value, and adding a condition to the queryBuilder. Receiving an array of filters from the request, we collect an array of classes that implement this interface (you can, by the way, make a collection, but this is optional) and loop through it in the repository and call a method for each element that adds conditions to the current queryBuilder.