Query Sorting Bundle
What does it do? :)
The query sorting bundle allows you to sort data from QueryBuilder and the Database. you can sort multiple columns at the same time and also you can sort relation fields with two-level deep and without any join in your query builder.Installation:
composer require bugloos/query-sorting-bundle
Compatibility
- PHP v7.4 or above
- Symfony v4.4 or above
Usage
Suppose our database has the following tables with the following relations
Now we want to show the Book entity by sorting
We want sort Book entity by price column, so we can send sort data by Querystring or make inline with array like this:
/*
* Sort book by price ascending
*/
//Get api/book/index?order[price]=ASC
OR
$orders = [
'price' => SortType::ASC,
];
/*
* Sort book by price descending
*/
//Get api/book/index?order[price]=DESC
OR
$orders = [
'price' => SortType::DESC,
];
You just need to add Sorting class in controller the call sort method:
use Bugloos\QuerySortingBundle\Service\QuerySorting;
The following code is in Book controller.
As you see, At first you should call for() method and pass QueryBuilder as parameter to this method
Then call parameters() method and pass orders request items
At the end you should call sort() method to run sorting
The return of sort method is Query Builder, so you can add anything else to Query Builder after sorting.public function index(
Request $request,
BookRepository $bookRepository,
QuerySorting $querySorting
): Response {
$queryBuilder = $bookRepository->createQueryBuilder('b');
$queryBuilder = $querySorting->for($queryBuilder)
->parameters($request->get('order'))
->sort()
;
return $queryBuilder->getQuery()->getResult();
}
If you want to sort the ManyToOne relation field or one level deep relation, you should add mapper.
To add a mapper, you call addMapper() method to add single mapper or call mappers() method to send multiple mappers with array
First parameter of addMapper() method is parameter name and second parameter is relation name and its field name, which separate by " . " sign
$mappers = [
'country' => 'country.name',
];
For example we want to sort Book entity by its Country name. Book has ManyToOne relation with Country entity
/*
* Sort book by Country name ascending
*/
//Get api/book/index?order[country]=ASC
OR
$orders = [
'country' => SortType::ASC,
];
/*
* Sort book by Country name descending
*/
//Get api/book/index?order[country]=DESC
OR
$orders = [
'country' => SortType::DESC,
];
The following code is in Book controller.
public function index(
Request $request,
BookRepository $bookRepository,
QuerySorting $querySorting
): Response {
$queryBuilder = $bookRepository->createQueryBuilder('b');
$queryBuilder = $querySorting->for($queryBuilder)
->parameters($request->get('order'))
->addMapper('country', 'country.name')
->sort()
;
return $queryBuilder->getQuery()->getResult();
}
NOTE: There is no need to add your relationship join in Query builder because if join is not added, I will add it automatically. ;)
$queryBuilder = $bookRepository->createQueryBuilder('b');
OR
$queryBuilder = $bookRepository->createQueryBuilder('b')
->addSelect('country')
->leftJoin('b.country', 'country')
;
If you want to sort the ManyToMany relation field or two level deep relation, you should again add mapper
$mapper = [
'age' => 'bookUsers.user.age',
];
For example we want to sort Book entity by its Writer age. Book has ManyToMany relation with User entity
/*
* Sort book by Writer age ascending
*/
//Get api/book/index?order[age]=ASC
OR
$orders = [
'age' => SortType::ASC,
];
/*
* Sort book by Writer age descending
*/
//Get api/book/index?order[age]=DESC
OR
$orders = [
'age' => SortType::DESC,
];
The following code is in Book controller.
public function index(
Request $request,
BookRepository $bookRepository,
QuerySorting $querySorting
): Response {
$queryBuilder = $bookRepository->createQueryBuilder('b');
$queryBuilder = $querySorting->for($queryBuilder)
->parameters($request->get('order'))
->addMapper('age', 'bookUsers.user.age')
->sort()
;
return $queryBuilder->getQuery()->getResult();
}
NOTE: You should know that you can sort data with multiple columns too, you just need to send multiple order data with a Query string like this:
/*
* Sort book by title ascending and price descending
*/
//Get api/book/index?order[title]=ASC&order[price]=DESC
OR
$orders = [
'title' => SortType::ASC,
'price' => SortType::DESC,
];
Suggestion
You can change two parameters with the config file, just make a yaml file in config/packages/ directory then you can change default cache time for queries and default relation separator as follows:
query_sorting:
default_cache_time: 3600
separator: '.'
NOTE: You can set the cache time for each query separately and if you don't set any cache time, it uses default cache time in your config file
$queryBuilder = $querySorting->for($queryBuilder)
->parameters($request->get('order'))
->cacheTime(120)
->sort()
;
Contributing 
If you find an issue, or have a better way to do something, feel free to open an issue or a pull request.
3 Aug 5, 2022
130 Dec 7, 2022
2k Dec 27, 2022
424 Dec 12, 2022
6.3k Jan 5, 2023
1k Dec 22, 2022
618 Dec 30, 2022
162 Dec 19, 2022
9 Jul 15, 2022
3 Feb 14, 2022
2 Mar 7, 2022
4 Feb 22, 2022
10 Jan 8, 2023
30 Oct 18, 2022
327 Dec 28, 2022
470 Dec 8, 2022
12 Dec 11, 2022
745 Jan 7, 2023
8 Oct 15, 2022