Elegant Filter, Search and Sort Traits in Laravel

BaseUITableFilter.php

<?php

namespace App\Support\UITableFilters;

use App\Traits\Filter\Table\CanFilter;
use App\Traits\Filter\Table\CanSort;
use App\Traits\Filter\Table\CanSearch;
use Exception;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\Validator;

class BaseUITableFilter
{
  use CanFilter, CanSearch, CanSort;

  protected Builder $baseQuery;
  protected array $requestData;
  protected array $extraValidationRules = [];

  protected const DELIMITER_RELATIONSHIP = '.';
  protected const DELIMITER_METHOD = '()';

  /**
   * This represents a column that is specific to the model e.g. 'id'.
   * If the default column is an existing relationship in the query ie. an already existing join, start the column with a dot e.g. '.meetings.id'
   * If the default column is a method in the model, end the column with a parenthesis e.g. 'active()' - This is specific to filtering
   */
  private const COLUMN_DEFAULT = 'COLUMN_DEFAULT';

  /**
   * This represents an array of columns that are specific to the model e.g. ['id', 'first_name']
   * If the default column (in the array) is an existing relationship in the query, start the column with a dot e.g. ['.meetings.id', '.users.first_name']
   * If the default column (in the array) is a method in the model, end the column with a parenthesis e.g. ['active()', 'offerred()']
   */
  private const COLUMN_DEFAULT_MERGED = 'COLUMN_DEFAULT_MERGED';

  /**
   * This represents a column that is specific to a single relationship e.g. 'roles.role'
   * If the column checks for the existence of a relationship, end the column with a dot e.g. 'roles.'
   */
  private const COLUMN_SINGLE_RELATIONSHIP = 'COLUMN_SINGLE_RELATIONSHIP';

  /**
   * This represents the columns that are specific to multiple relationships e.g. 'users.roles.role'
   * If the column checks for the existence of a relationship, end the column with a dot e.g. 'users.roles.'
   */
  private const COLUMN_MULTIPLE_RELATIONSHIP = 'COLUMN_MULTIPLE_RELATIONSHIP';

  /**
   * This represents an array of columns that are specific to single relationships e.g. ['roles.id', 'workers.id']
   * If the column checks for the existence of a relationship, end the column with a dot e.g. ['roles.', 'workers.']
   */
  private const COLUMN_SINGLE_RELATIONSHIP_MERGED = 'COLUMN_SINGLE_RELATIONSHIP_MERGED';

  /**
   * This represents an array of columns that are specific to multiple relationships e.g. ['users.roles.role', 'companies.users.first_name']
   * If the column checks for the existence of a relationship, end the column with a dot e.g. ['users.roles.', 'companies.users.']
   */
  private const COLUMN_MULTIPLE_RELATIONSHIP_MERGED = 'COLUMN_MULTIPLE_RELATIONSHIP_MERGED';

  public function __construct(array $requestData, Builder $baseQuery)
  {
    $this->requestData = $requestData;
    $this->baseQuery = $baseQuery;

    $this->validateRequestData();
  }

  public function validateRequestData(): static
  {
    $this->requestData = Validator::validate($this->requestData, [
      'searchField' => [
        'required_with:searchQuery',
        function ($attribute, $value, $fail) {
          if (!is_string($value) && !is_array($value)) {
            $fail($attribute . ' must be a string or an array.');
          }
        },
      ],
      'searchQuery' => [
        'required_with:searchField',
        function ($attribute, $value, $fail) {
          if (!is_string($value) && !is_array($value)) {
            $fail($attribute . ' must be a string or an array.');
          }
        },
      ],
      'sortDir' => ['required_with:sortKey', 'string'],
      'sortKey' => ['required_with:sortDir', 'string'],
      ...$this->extraValidationRules,
    ]);

    return $this;
  }

  /**
   * This solves the problem of ambiguous column names (mostly id) when using relationships.
   */
  private function getRelationshipTable(string $relationship): string
  {
    $relationship = $this->baseQuery->getModel()->$relationship();

    return $relationship->getRelated()->getTable();
  }

  private function splitSingleRelationshipColumn(string $column): array
  {
    [$relationship, $column] = explode(self::DELIMITER_RELATIONSHIP, $column);

    /**
     * @var string $relationship
     * @var string $column
     */
    return [$relationship, $column];
  }

  private function splitMultipleRelationshipColumn(string $column): array
  {
    $lastDelimiterPosition = strrpos($column, self::DELIMITER_RELATIONSHIP);

    $relationship = substr($column, 0, $lastDelimiterPosition);
    $leftRelationship = substr(
      $relationship,
      0,
      strpos($relationship, self::DELIMITER_RELATIONSHIP)
    );
    $rightRelationship = substr(
      $relationship,
      strpos($relationship, self::DELIMITER_RELATIONSHIP) + 1
    );
    $column = substr($column, $lastDelimiterPosition + 1);

    /**
     * @var string $relationship
     * @var string $leftRelationship
     * @var string $rightRelationship
     * @var string $column
     */
    return [$relationship, $leftRelationship, $rightRelationship, $column];
  }

  protected function isColumnDefaultMethod(string|array $column): bool
  {
    return substr($column, -2) === self::DELIMITER_METHOD;
  }

  private function isColumnDefaultExistingRelationship(string $column): bool
  {
    return strpos($column, self::DELIMITER_RELATIONSHIP) === false ||
      strpos($column, self::DELIMITER_RELATIONSHIP) === 0;
  }

  private function isColumnDefault(string|array $column): bool
  {
    return is_string($column) &&
      $this->isColumnDefaultExistingRelationship($column);
  }

  private function isColumnDefaultMerged(string|array $column): bool
  {
    return is_array($column) &&
      $this->isColumnDefaultExistingRelationship($column[0]);
  }

  private function isColumnSingleRelationship(string|array $column): bool
  {
    return is_string($column) &&
      substr_count($column, self::DELIMITER_RELATIONSHIP) === 1;
  }

  private function isColumnMultipleRelationship(string|array $column): bool
  {
    return is_string($column) &&
      substr_count($column, self::DELIMITER_RELATIONSHIP) > 1;
  }

  private function isColumnSingleRelationshipMerged(string|array $column): bool
  {
    return is_array($column) &&
      substr_count($column[0], self::DELIMITER_RELATIONSHIP) === 1;
  }

  private function isColumnIsMultipleRelationshipMerged(
    string|array $column
  ): bool {
    return is_array($column) &&
      substr_count($column[0], self::DELIMITER_RELATIONSHIP) > 1;
  }

  private function getColumnType(string|array $column): string
  {
    return match (true) {
      $this->isColumnDefault($column) => self::COLUMN_DEFAULT,
      $this->isColumnDefaultMerged($column) => self::COLUMN_DEFAULT_MERGED,
      $this->isColumnSingleRelationship(
        $column
      ) => self::COLUMN_SINGLE_RELATIONSHIP,
      $this->isColumnMultipleRelationship(
        $column
      ) => self::COLUMN_MULTIPLE_RELATIONSHIP,
      $this->isColumnSingleRelationshipMerged(
        $column
      ) => self::COLUMN_SINGLE_RELATIONSHIP_MERGED,
      $this->isColumnIsMultipleRelationshipMerged(
        $column
      ) => self::COLUMN_MULTIPLE_RELATIONSHIP_MERGED,
      default => throw new Exception('Invalid column type'),
    };
  }

  protected function callFilterAction(
    string $action,
    array|string $column,
    string|array $query
  ): void {
    switch ($this->getColumnType($column)) {
      case self::COLUMN_DEFAULT:
        if ($this->isColumnDefaultExistingRelationship($column)) {
          $column = ltrim($column, self::DELIMITER_RELATIONSHIP);
        }

        $this->{$action . 'DefaultColumn'}($column, $query);
        break;

      case self::COLUMN_DEFAULT_MERGED:
        if ($this->isColumnDefaultExistingRelationship($column[0])) {
          $column = array_map(
            fn($column) => ltrim($column, self::DELIMITER_RELATIONSHIP),
            $column
          );
        }

        $this->{$action . 'DefaultMergedColumns'}($column, $query);
        break;

      case self::COLUMN_SINGLE_RELATIONSHIP:
        $this->{$action . 'SingleRelationshipColumn'}($column, $query);
        break;

      case self::COLUMN_MULTIPLE_RELATIONSHIP:
        $this->{$action . 'MultipleRelationshipColumn'}($column, $query);
        break;

      case self::COLUMN_SINGLE_RELATIONSHIP_MERGED:
        $this->{$action . 'SingleRelationshipMergedColumns'}($column, $query);
        break;

      case self::COLUMN_MULTIPLE_RELATIONSHIP_MERGED:
        $this->{$action . 'MultipleRelationshipMergedColumns'}($column, $query);
        break;

      default:
        throw new Exception('Invalid column type');
        break;
    }
  }
}

CanFilter.php

<?php

namespace App\Traits\Filter\Table;

trait CanFilter
{
  // This logic is specific to the table filter
  private function getModelMethod(string $column): string
  {
    return rtrim($column, '()');
  }

  public function filterQuery(): self
  {
    $filterQueryArray = array_filter(
      $this->requestData,
      function ($key) {
        return array_key_exists($key, $this->filterableColumns);
      },
      ARRAY_FILTER_USE_KEY
    );

    $filterQueryArray = array_map(
      function ($key, $value) {
        return [$this->filterableColumns[$key] => $value];
      },
      array_keys($filterQueryArray),
      $filterQueryArray
    );

    if (empty($filterQueryArray)) {
      return $this;
    }

    $filterQueryArray = array_merge(...$filterQueryArray);

    foreach ($filterQueryArray as $columnName => $filterQuery) {
      $this->callFilterAction('filter', $columnName, $filterQuery);
    }

    return $this;
  }

  protected function filterDefaultColumn(
    string $column,
    string|array $filterQuery
  ): void {
    if ($this->isColumnDefaultMethod($column)) {
      $this->baseQuery->{$this->getModelMethod($column)}();
      return;
    }

    $this->baseQuery->when(
      is_array($filterQuery),
      fn($q) => $q->whereIn($column, $filterQuery),
      fn($q) => $q->where($column, $filterQuery)
    );
  }

  protected function filterDefaultMergedColumns(
    array $columns,
    string $filterQuery
  ): void {
    if ($this->isColumnDefaultMethod($columns[0])) {
      foreach ($columns as $column) {
        $this->baseQuery->{$this->getModelMethod($column)}();
      }

      return;
    }

    $this->baseQuery->where(function ($query) use ($columns, $filterQuery) {
      foreach ($columns as $column) {
        $query->orWhere($column, $filterQuery);
      }
    });
  }

  protected function filterSingleRelationshipColumn(
    string $column,
    string $filterQuery
  ): void {
    [$relationship, $column] = $this->splitSingleRelationshipColumn($column);

    if (empty($column)) {
      $this->baseQuery->whereHas($relationship);

      return;
    }

    $this->baseQuery->whereHas($relationship, function ($query) use (
      $relationship,
      $column,
      $filterQuery
    ) {
      $relationshipTable = $this->getRelationshipTable($relationship);

      $query->where($relationshipTable . '.' . $column, $filterQuery);
    });
  }

  protected function filterMultipleRelationshipColumn(
    string $column,
    string $filterQuery
  ): void {
    [$relationship, $column] = $this->splitMultipleRelationshipColumn($column);

    if (empty($column)) {
      $this->baseQuery->powerJoinWhereHas($relationship);

      return;
    }

    $this->baseQuery->powerJoinWhereHas($relationship, function ($query) use (
      $column,
      $filterQuery
    ) {
      $query->where($column, $filterQuery);
    });
  }

  protected function filterSingleRelationshipMergedColumns(
    array $columns,
    string $filterQuery
  ): void {
    [$relationship, $columns] = $this->splitSingleRelationshipColumn(
      $columns[0]
    );

    if (empty($columns)) {
      $this->baseQuery->whereHas($relationship);

      return;
    }

    $this->baseQuery->whereHas($relationship, function ($query) use (
      $relationship,
      $columns,
      $filterQuery
    ) {
      $relationshipTable = $this->getRelationshipTable($relationship);

      foreach ($columns as $column) {
        $query->orWhere($relationshipTable . '.' . $column, $filterQuery);
      }
    });
  }

  protected function filterMultipleRelationshipMergedColumns(
    array $columns,
    string $filterQuery
  ): void {
    [$relationship, $columns] = $this->splitMultipleRelationshipColumn(
      $columns[0]
    );

    if (empty($columns)) {
      $this->baseQuery->powerJoinWhereHas($relationship);

      return;
    }

    $this->baseQuery->powerJoinWhereHas($relationship, function ($query) use (
      $columns,
      $filterQuery
    ) {
      foreach ($columns as $column) {
        $query->orWhere($column, $filterQuery);
      }
    });
  }
}

CanSearch.php

<?php

namespace App\Traits\Filter\Table;

trait CanSearch
{
  private function processSingleSearch(): void
  {
    $searchQuery = $this->requestData['searchQuery'] ?? null;
    $searchField = $this->requestData['searchField'] ?? null;
    $columnName = $this->searchableColumns[$searchField] ?? null;

    if (empty($searchQuery) || empty($columnName)) {
      return;
    }

    $this->callFilterAction('search', $columnName, $searchQuery);
  }

  private function processArraySearch(): void
  {
    foreach ($this->requestData['searchQuery'] as $index => $query) {
      $columnName =
        $this->searchableColumns[$this->requestData['searchField'][$index]];
      $this->callFilterAction('search', $columnName, $query);
    }
  }

  public function searchQuery(): self
  {
    if (
      !isset($this->requestData['searchQuery']) ||
      !isset($this->requestData['searchField'])
    ) {
      return $this;
    }

    if (is_array($this->requestData['searchQuery'])) {
      $this->processArraySearch();
    } else {
      $this->processSingleSearch();
    }

    return $this;
  }

  protected function searchDefaultColumn(
    string $column,
    string $searchQuery
  ): void {
    $this->baseQuery->where($column, 'ilike', "%{$searchQuery}%");
  }

  protected function searchDefaultMergedColumns(
    array $columns,
    string $searchQuery
  ): void {
    $this->baseQuery->where(function ($query) use ($columns, $searchQuery) {
      foreach ($columns as $column) {
        $query->orWhere($column, 'ilike', "%{$searchQuery}%");
      }
    });
  }

  protected function searchSingleRelationshipColumn(
    string $column,
    string $searchQuery
  ): void {
    [$relationship, $column] = $this->splitSingleRelationshipColumn($column);

    $this->baseQuery->whereHas($relationship, function ($query) use (
      $column,
      $searchQuery
    ) {
      $query->where($column, 'ilike', "%{$searchQuery}%");
    });
  }

  protected function searchMultipleRelationshipColumn(
    string $column,
    string $searchQuery
  ): void {
    [
      $relationship,
      ,
      $rightRelationship,
      $column,
    ] = $this->splitMultipleRelationshipColumn($column);

    $this->baseQuery->joinRelationship($relationship, [
      $rightRelationship => function ($join) use ($column, $searchQuery) {
        $join->where($column, 'ilike', "%{$searchQuery}%");
      },
    ]);
  }

  protected function searchSingleRelationshipMergedColumns(
    array $columns,
    string $searchQuery
  ): void {
    [$relationship] = $this->splitSingleRelationshipColumn($columns[0]);

    $this->baseQuery->whereHas($relationship, function ($query) use (
      $columns,
      $searchQuery
    ) {
      $query->where(function ($query) use ($columns, $searchQuery) {
        foreach ($columns as $column) {
          [, $column] = $this->splitSingleRelationshipColumn($column);

          $query->orWhere($column, 'ilike', "%{$searchQuery}%");
        }
      });
    });
  }

  /**
   * @see https://github.com/kirschbaum-development/eloquent-power-joins#1---join-relationship
   */
  protected function searchMultipleRelationshipMergedColumns(
    array $columns,
    string $searchQuery
  ): void {
    [
      $relationship,
      ,
      $rightRelationship,
      ,
    ] = $this->splitMultipleRelationshipColumn($columns[0]);

    $this->baseQuery->joinRelationship($relationship, [
      $rightRelationship => function ($join) use ($columns, $searchQuery) {
        $join->where(function ($join) use ($columns, $searchQuery) {
          foreach ($columns as $column) {
            [, , , $column] = $this->splitMultipleRelationshipColumn($column);
            $join->orWhere($column, 'ilike', "%{$searchQuery}%");
          }
        });
      },
    ]);
  }
}

CanSort.php

<?php

namespace App\Traits\Filter\Table;

trait CanSort
{
  public function sortQuery(): self
  {
    $sortDir = $this->requestData['sortDir'] ?? null;
    $sortKey = $this->requestData['sortKey'] ?? null;
    $columnName = $this->sortableColumns[$sortKey] ?? null;

    if (empty($columnName)) {
      return $this;
    }

    $this->callFilterAction('sort', $columnName, $sortDir);

    return $this;
  }

  protected function sortDefaultColumn(string $column, string $sortDir): void
  {
    $this->baseQuery->orderBy($column, $sortDir);
  }

  protected function sortDefaultMergedColumns(
    array $columns,
    string $sortDir
  ): void {
    foreach ($columns as $column) {
      $this->sortDefaultColumn($column, $sortDir);
    }
  }

  /**
   * @see https://github.com/kirschbaum-development/eloquent-power-joins#3---order-by
   */
  protected function sortSingleRelationshipColumn(
    string $column,
    string $sortDir
  ): void {
    $this->baseQuery->orderByLeftPowerJoins($column, $sortDir);
  }

  protected function sortMultipleRelationshipColumn(
    string $column,
    string $sortDir
  ): void {
    $this->sortSingleRelationshipColumn($column, $sortDir);
  }

  protected function sortSingleRelationshipMergedColumns(
    array $columns,
    string $sortDir
  ): void {
    foreach ($columns as $column) {
      $this->sortSingleRelationshipColumn($column, $sortDir);
    }
  }

  protected function sortMultipleRelationshipMergedColumns(
    array $columns,
    string $sortDir
  ): void {
    foreach ($columns as $column) {
      $this->sortMultipleRelationshipColumn($column, $sortDir);
    }
  }
}

ListPaymentsController.php

<?php

namespace App\Http\Controllers\Payments;

use App\Http\Controllers\Controller;
use App\Models\Payment;
use App\Support\UITableFilters\PaymentUITableFilters;

class ListPaymentsController extends Controller
{
  public function __invoke()
  {
    $this->authorize('viewAny', Payment::class);

    $query = Payment::query()
      ->with(['payer', 'payable']);

    PaymentUITableFilters::make(request()->all(), $query)
      ->filterQuery()
      ->searchQuery()
      ->sortQuery();

    //     https://myray.app/
    ray($query);
  }
}

PaymentUITableFilters.php

<?php

namespace App\Support\UITableFilters;

use Illuminate\Database\Eloquent\Builder;

class PaymentUITableFilters extends BaseUITableFilter
{
  protected array $sortableColumns = [
    'createdAt' => 'created_at',
    'amount' => 'amount',
    'clientName' => [
      'payerUser.first_name',
      'payerUser.last_name',
    ],
    'consultantName' => [
      'payableApplicationInstance.consultant.first_name',
      'payableApplicationInstance.consultant.last_name',
    ],
  ];

  protected array $searchableColumns = [
    'notes' => 'notes',
    'referenceCode' => 'payableApplicationInstance.reference_code',
    'consultantName' => [
      'payableApplicationInstance.consultant.first_name',
      'payableApplicationInstance.consultant.last_name',
    ],
    'userName' => ['payerUser.first_name', 'payerUser.last_name'],
  ];

  protected array $filterableColumns = [
    'status' => 'status',
    'source' => 'source',
    'startDate' => 'dateRange()',
    'startPaymentDate' => 'paymentDateRange()',
  ];

  protected array $extraValidationRules = [
    'status' => ['sometimes', 'string'],
    'source' => ['sometimes', 'string'],
    'endDate' => ['sometimes', 'string', 'required_with:startDate'],
    'endPaymentDate' => [
      'sometimes',
      'string',
      'required_with:startPaymentDate',
    ],
  ];

  public static function make(array $requestData, Builder $baseQuery): self
  {
    return new self($requestData, $baseQuery);
  }
}