Query Sorting Bundle Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require bugloos/query-sorting-bundle
   

   Ensure your project meets the requirements (PHP 8.1+, Symfony 4.4+).

2. Enable the Bundle: Add to config/bundles.php:

   Bugloos\QuerySortingBundle\QuerySortingBundle::class => ['all' => true],
   

3. First Use Case: Sort a basic Eloquent query via query string:

   use Bugloos\QuerySortingBundle\SortType;
   
   $books = Book::query()->sort(['price' => SortType::ASC])->get();
   

   Or via URL (e.g., /api/books?order[price]=ASC).

Where to Look First

• Documentation: The README covers core features (multi-column, nested relations).
• Tests: /tests/Fixtures/ demonstrates real-world usage (e.g., sorting Book by author.name).
• Service Container: The bundle registers a QuerySortingService for dependency injection.

Implementation Patterns

Core Workflows

1. Query String Sorting: Parse and apply sorting from HTTP requests:

   use Bugloos\QuerySortingBundle\QueryStringParser;
   
   $parser = new QueryStringParser();
   $orders = $parser->parse(request()->query->get('order'));
   $books = Book::query()->sort($orders)->get();
   

2. Inline Array Sorting: Define sorts programmatically:

   $orders = [
       'price' => SortType::DESC,
       'author.name' => SortType::ASC, // Nested relation
   ];
   $books = Book::query()->sort($orders)->get();
   

3. Dynamic Sorting in Controllers:

   public function index(Request $request)
   {
       $books = Book::query()
           ->sort($request->get('order', []))
           ->paginate(10);
       return response()->json($books);
   }
   

Integration Tips

• APIs: Use QueryStringParser to standardize sorting across endpoints.
• Admin Panels: Expose sortable columns in UI (e.g., DataTables) via order[] params.
• Caching: Cache parsed $orders arrays if sorting logic is reused (e.g., in a service layer).
• Validation: Validate order[] params early (e.g., with Symfony Validator):

  $this->validate(request(), ['order' => 'array']);
  

Advanced Patterns

1. Custom Sorting Logic: Extend the bundle’s SortableInterface for domain-specific rules:

   class CustomBookSort implements SortableInterface
   {
       public function apply(SortDefinition $definition, QueryBuilder $query)
       {
           // Custom logic (e.g., sort by `price * discount`)
       }
   }
   

   Register via services.yaml:

   bugloos.query_sorting.sortables:
       custom_book_sort: Bugloos\QuerySortingBundle\Tests\Fixtures\CustomBookSort
   

2. Relation Sorting Without Joins: Sort by nested relations (e.g., author.name) without JOIN:

   $orders = ['author.name' => SortType::ASC];
   $books = Book::query()->sort($orders)->get();
   

   Note: The bundle handles this via subqueries under the hood.

3. Dynamic Column Whitelisting: Restrict sortable columns to prevent SQL injection:

   $books = Book::query()
       ->sort($orders, ['price', 'author.name', 'title'])
       ->get();
   

Gotchas and Tips

Pitfalls

1. Nested Relation Limits:

   • The bundle supports 2-level deep relations (e.g., author.name).
   • Attempting deeper nesting (e.g., author.address.city) will fail silently. Use explicit JOIN for deeper sorts.

2. Case Sensitivity:

   • Sorting on ASC/DESC is case-insensitive in query strings but strict in arrays:

     // Works
     $orders = ['price' => 'ASC'];
     
     // Fails (throws exception)
     $orders = ['price' => 'asc'];
     

   • Use SortType::ASC constants for reliability.

3. Performance with Large Datasets:

   • Nested relation sorts generate subqueries, which can be slow on large tables.
   • Mitigation: Add database indexes on sorted columns (e.g., ALTER TABLE books ADD INDEX idx_price).

4. Query String Parsing Quirks:

   • Empty order[] params may not be parsed correctly. Validate with:

     $orders = $parser->parse($request->query->get('order', []));
     

Debugging

1. Log Raw SQL: Enable Laravel’s query logging to inspect generated sorts:

   \DB::enableQueryLog();
   $books = Book::query()->sort($orders)->get();
   dd(\DB::getQueryLog());
   

2. Validate Sort Definitions: Use the SortDefinition class to debug parsed orders:

   $definition = new SortDefinition($orders);
   dd($definition->getSorts()); // Array of [column => direction]
   

3. Common Errors:

   • ColumnNotFoundException: The column/relation doesn’t exist. Verify table/relation names.
   • InvalidSortTypeException: Use SortType::ASC/SortType::DESC instead of strings.

Configuration Quirks

1. Default Sorting: The bundle doesn’t enforce defaults. Set them in your query:

   $books = Book::query()->sort(['price' => SortType::DESC])->get();
   

2. Custom Sortables:

   • Register custom sortables in config/packages/bugloos_query_sorting.yaml:

     sortables:
         App\Sortables\CustomBookSort: ~
     

   • Or via DI container (as shown in Advanced Patterns).

3. Symfony Flex Autoloading: If using Symfony Flex, ensure the bundle’s autoloader is registered:

   composer dump-autoload
   

Extension Points

1. Event Listeners: Listen to query.sorting events to modify sorts dynamically:

   use Bugloos\QuerySortingBundle\Event\SortingEvent;
   
   public function onSorting(SortingEvent $event)
   {
       $event->getDefinition()->addSort('created_at', SortType::DESC);
   }
   

   Register in services.yaml:

   Bugloos\QuerySortingBundle\EventListener\SortingListener:
       tags:
           - { name: kernel.event_listener, event: query.sorting, method: onSorting }
   

2. Custom Sort Types: Extend SortType for domain-specific directions (e.g., SortType::CUSTOM):

   class ExtendedSortType extends SortType
   {
       public const CUSTOM = 'custom';
   }
   

3. Database-Agnostic Sorting: Override the QueryBuilder adapter for non-MySQL databases (e.g., PostgreSQL):

   use Bugloos\QuerySortingBundle\Adapter\QueryBuilderAdapterInterface;
   
   class PostgreSqlAdapter implements QueryBuilderAdapterInterface
   {
       public function applySort(QueryBuilder $query, SortDefinition $definition)
       {
           // Custom PostgreSQL syntax (e.g., ILIKE for case-insensitive sorts)
       }
   }
   

   Bind in services.yaml:

   Bugloos\QuerySortingBundle\Adapter\QueryBuilderAdapterInterface: '@App\Adapter\PostgreSqlAdapter'