Query Sorting Bundle Laravel Package

composer require bugloos/query-sorting-bundle

• PHP v8.1 or above
• Symfony v4.4 or above

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.

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();
}

$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')      
;

$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,
];

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()
;

If you find an issue, or have a better way to do something, feel free to open an issue or a pull request.