Master

Query Filter Bundle

Query Filter Bundle brings request filtering and pagination functionality to Symfony 4 applications that use Doctrine 2.

Installation

First, install the dependency:

$ composer require artprima/query-filter-bundle

Usage examples

Basic example

• Controller

<?phpnamespaceApp\Controller;
useArtprima\QueryFilterBundle\QueryFilter\Config\BaseConfig; useSymfony\Bundle\FrameworkBundle\Controller\Controller;
useSensio\Bundle\FrameworkExtraBundle\Configuration\Route;
useSymfony\Component\HttpFoundation\RequestasHttpRequest;
useArtprima\QueryFilterBundle\Request\Request;
useArtprima\QueryFilterBundle\QueryFilter\QueryFilter;
useArtprima\QueryFilterBundle\Response\Response;
useApp\Repository\ItemRepository;
class DefaultController extends Controller
{
// .../** * @Route("/")  */publicfunctionindexAction(HttpRequest$request, ItemRepository$repository)
{
// set up the config$config = newBaseConfig();
$config->setSearchAllowedCols(['t.name']);
$config->setAllowedLimits([10, 25, 50, 100]);
$config->setDefaultLimit(10);
$config->setSortCols(['t.id'], ['t.id' => 'asc']);
$config->setRequest(newRequest($request));
// here we provide a repository callback that will be used internally in the QueryFilter// The signature of the method must be as follows: function functionName(QueryFilterArgs $args): QueryResult;$config->setRepositoryCallback([$repository, 'findByOrderBy']);
// Response must implement Artprima\QueryFilterBundle\Response\ResponseInterface$queryFilter = newQueryFilter(Response::class);
/** @var Response $data the type of the variable is defined by the class in the first argument of QueryFilter's constructor */$response = $queryFilter->getData($config);
$data = $response->getData();
$meta = $response->getMeta();
// ... now do something with $data or $meta
}
// ...
}

• Repository

<?phpnamespaceApp\Repository;
useApp\Entity\Item;
useArtprima\QueryFilterBundle\Query\Mysql\PaginationWalker;
useArtprima\QueryFilterBundle\Query\ConditionManager;
useArtprima\QueryFilterBundle\Query\ProxyQueryBuilder;
useArtprima\QueryFilterBundle\QueryFilter\QueryFilterArgs;
useArtprima\QueryFilterBundle\QueryFilter\QueryResult;
useDoctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
useSymfony\Bridge\Doctrine\RegistryInterface;
class ItemRepository extends ServiceEntityRepository
{
/** * @var ConditionManager */private$pqbManager;
publicfunction__construct(RegistryInterface$registry, ConditionManager$manager)
{
parent::__construct($registry, Item::class);
$this->pqbManager = $manager;
}
publicfunctionfindByOrderBy(QueryFilterArgs$args): QueryResult
{
// Build our request$qb = $this->createQueryBuilder('t')
->setFirstResult($args->getOffset())
->setMaxResults($args->getLimit());
$proxyQb = newProxyQueryBuilder($qb, $this->pqbManager);
$qb = $proxyQb->getSortedAndFilteredQueryBuilder($args->getSearchBy(), $args->getSortBy());
$query = $qb->getQuery();
$query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, PaginationWalker::class);
$query->setHint("mysqlWalker.sqlCalcFoundRows", true);
$result = $query->getResult();
$totalRows = $this->_em->getConnection()->query('SELECT FOUND_ROWS()')->fetchColumn();
// return the wrapped resultreturnnewQueryResult($result, $totalRows);
} // ...
}

Now you can start your php server and filter the requests:

GET http://127.0.0.1:8000/?filter[t.name]=Doe&limit=100

This request will perform a LIKE request in DQL:

SELECT t FROM Item WHEREt.nameLIKE"%Doe%"LIMIT100

Advanced example

This filtering library is best used together with JMSSerializerBundle and FOSRestBundle. You will eventually write a lot less code that it was shown in the basic example.

To utilize the advanced usage, install all the packages.

composer require friendsofsymfony/rest-bundle
composer require jms/serializer-bundle
composer require artprima/query-filter-bundle

• Turn them on in config/bundles.php:

<?phpreturn [
// ...Artprima\QueryFilterBundle\ArtprimaQueryFilterBundle::class => ['all' => true],
FOS\RestBundle\FOSRestBundle::class => ['all' => true],
JMS\SerializerBundle\JMSSerializerBundle::class => ['all' => true],
// ...
];

NOTE: you may need to add further bundles depending on your set up for FOSRestBundle and/or JMSSerializerBundle.

• Controller:

<?phpnamespaceApp\Controller;
useApp\QueryFilter\Response;
useApp\Repository\ItemRepository;
useArtprima\QueryFilterBundle\QueryFilter\Config\ConfigInterfaceasQueryFilterConfigInterface;
useFOS\RestBundle\Controller\AnnotationsasRest;
useFOS\RestBundle\Controller\FOSRestController;
useFOS\RestBundle\Routing\ClassResourceInterface;
useSensio\Bundle\FrameworkExtraBundle\Configuration\ParamConverter;
useArtprima\QueryFilterBundle\Controller\Annotations\QueryFilter;
class ItemController extends FOSRestController implements ClassResourceInterface
{
/** * @Rest\View(serializerEnableMaxDepthChecks=true) * @ParamConverter("config", class="App\QueryFilter\Config\ItemConfig", * converter="query_filter_config_converter", * options={"entity_class": "App\Entity\Item", "repository_method": "findByOrderBy"}) * @QueryFilter() * @Rest\Get("/items") */publicfunctioncgetAction(QueryFilterConfigInterface$config)
{
return$config;
}
}

• Repository:

<?phpnamespaceApp\Repository;
useApp\Entity\Item;
useArtprima\QueryFilterBundle\Query\Mysql\PaginationWalker;
useArtprima\QueryFilterBundle\Query\ConditionManager;
useArtprima\QueryFilterBundle\Query\ProxyQueryBuilder;
useArtprima\QueryFilterBundle\QueryFilter\QueryFilterArgs;
useArtprima\QueryFilterBundle\QueryFilter\QueryResult;
useDoctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
useSymfony\Bridge\Doctrine\RegistryInterface;
/** * @method Item|null find($id, $lockMode = null, $lockVersion = null) * @method Item|null findOneBy(array $criteria, array $orderBy = null) * @method Item[] findAll() * @method Item[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null) */class ItemRepository extends ServiceEntityRepository
{
/** * @var ConditionManager */private$pqbManager;
publicfunction__construct(RegistryInterface$registry, ConditionManager$manager)
{
parent::__construct($registry, Item::class);
$this->pqbManager = $manager;
}
publicfunctionfindByOrderBy(QueryFilterArgs$args): QueryResult
{
$qb = $this->createQueryBuilder('t')
->setFirstResult($args->getOffset())
->setMaxResults($args->getLimit());
$proxyQb = newProxyQueryBuilder($qb, $this->pqbManager);
$qb = $proxyQb->getSortedAndFilteredQueryBuilder($args->getSearchBy(), $args->getSortBy());
$query = $qb->getQuery();
$query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, PaginationWalker::class);
$query->setHint("mysqlWalker.sqlCalcFoundRows", true);
$result = $query->getResult();
$totalRows = $this->_em->getConnection()->query('SELECT FOUND_ROWS()')->fetchColumn();
returnnewQueryResult($result, $totalRows);
}
}

ItemConfig:

<?phpnamespaceApp\QueryFilter\Config;
useArtprima\QueryFilterBundle\QueryFilter\Config\BaseConfig;
class ItemConfig extends BaseConfig
{
publicfunction__construct()
{
$this->setSearchAllowedCols(array(
't.name',
));
$this->setSortCols(
array(
't.id',
),
array('t.id' => 'desc') // default
);
}
}

Simple Query Filter Examples

NOTE: assume that all the used fields are enabled in the configuration

• Performs t.name LIKE %doe% comparison
  • http://127.0.0.1:8000/?filter[t.name]=doe
• Performs t.name = "Doe" comparison
  • http://127.0.0.1:8000/?filter[t.name][type]=eq&filter[t.name][x]=Doe
• Performs t.name <> "Doe" comparison
  • http://127.0.0.1:8000/?filter[t.name][type]=not%20eq&filter[t.name][x]=Doe
• Performs t.name LIKE "Doe" comparison
  • http://127.0.0.1:8000/?filter[t.name][type]=like&filter[t.name][x]=Doe
• Performs t.name NOT LIKE "Doe" comparison
  • http://127.0.0.1:8000/?filter[t.name][type]=not%20like&filter[t.name][x]=Doe
• Performs t.frequency BETWEEN 8 AND 10 comparison
  • http://127.0.0.1:8000/?filter[t.name][type]=between&filter[t.frequency][x]=8&filter[t.frequency][x]=10
• Performs t.frequency NOT BETWEEN 8 AND 10 comparison
  • http://127.0.0.1:8000/?filter[t.name][type]=not%20between&filter[t.frequency][x]=8&filter[t.frequency][x]=10
• Performs t.frequency > 7 comparison
  • http://127.0.0.1:8000/?filter[t.name][type]=gt&filter[t.frequency][x]=7
• Performs t.frequency >= 7 comparison
  • http://127.0.0.1:8000/?filter[t.name][type]=gte&filter[t.frequency][x]=7
• Performs t.frequency IN (1, 2, 3, 4, 5) comparison
  • http://127.0.0.1:8000/?filter[t.name][type]=in&filter[t.frequency][x]=1,2,3,4,5
• Performs t.frequency NOT IN (1, 2, 3, 4, 5) comparison
  • http://127.0.0.1:8000/?filter[t.name][type]=in&filter[t.frequency][x]=1,2,3,4,5
• Performs t.description IS NULL comparison
  • http://127.0.0.1:8000/?filter[t.description][type]=is%20null
• Performs t.description IS NOT NULL comparison
  • http://127.0.0.1:8000/?filter[t.description][type]=is%20not%20null
• Performs t.frequency < 7 comparison
  • http://127.0.0.1:8000/?filter[t.name][type]=lt&filter[t.frequency][x]=7
• Performs t.frequency <= 7 comparison
  • http://127.0.0.1:8000/?filter[t.name][type]=lte&filter[t.frequency][x]=7
• Combined comparison t.frequency < 7 AND t.monetary > 50
  • http://127.0.0.1:8000/?filter[t.frequency][type]=lt&filter[t.frequency][x]=7&filter[t.monetary][type]=gt&filter[t.monetary][x]=50

Advanced Query Filter Example

Simple mode should be enough for most of the cases, however sometimes we might need to build more complicated filters having one and the same field used.

• Performs t.frequency = 10 OR t.frequency >= 85 (NOTE: filter[1][connector]=or - connector can be and (default) or or; connector used on the first filter has no effect)
  • http://127.0.0.1:8000/?simple=0&filter[0][field]=t.frequency&filter[0][type]=eq&filter[0][x]=10&filter[1][field]=t.frequency&filter[1][type]=gte&filter[1][x]=85&filter[1][connector]=or

Pagination Examples

• Second page (NOTE: if page is not given it defaults to 1)
  • http://127.0.0.1:8000/?page=2
• Limit records to 100 (NOTE: if default limits were provided and limit is not within the allowed values, it will be reset to the default value)
  • http://127.0.0.1:8000/?limit=100

Sorting Example

• Performs ORDER BY t.userId DESC (if sortdir is not given it defaults to asc)
  • http://127.0.0.1:8000/?sortby=t.userId&sortdir=desc

NOTE: at the moment this bundle doesn't support more than one field for ORDER BY.

This document is not finished yet, more examples will follow.

Code license

You are free to use the code in this repository under the terms of the MIT license. LICENSE contains a copy of this license.