REST API Development

Filtering, Sorting & Searching APIs

22 min Lesson 13 of 35

Introduction to API Query Features

Modern APIs must provide flexible ways for clients to query, filter, sort, and search through large datasets. Without these capabilities, clients would need to fetch all data and filter it locally—an inefficient and impractical approach. In this comprehensive lesson, we'll explore Laravel patterns for implementing robust filtering, sorting, and searching capabilities in your REST APIs.

Why Query Features Matter

Effective query capabilities are essential for creating powerful and user-friendly APIs:

Benefits of Advanced Query Features:

Performance: Reduces data transfer by returning only requested records
Flexibility: Empowers clients to fetch exactly what they need
User Experience: Enables real-time search, dynamic filters, and custom sorting
Bandwidth Efficiency: Minimizes payload size by filtering at the database level
Scalability: Handles large datasets without overwhelming clients
Developer Experience: Provides intuitive API interfaces for frontend developers

Basic Filtering with Query Parameters

The most common approach to filtering is using URL query parameters:

<?php
// app/Http/Controllers/Api/PostController.php

namespace App\Http\Controllers\Api;

use App\Models\Post;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;

class PostController extends Controller
{
    public function index(Request $request): JsonResponse
    {
        $query = Post::query();

        // Filter by status
        if ($request->has('status')) {
            $query->where('status', $request->status);
        }

        // Filter by author
        if ($request->has('author_id')) {
            $query->where('author_id', $request->author_id);
        }

        // Filter by date range
        if ($request->has('from_date')) {
            $query->where('created_at', '>=', $request->from_date);
        }

        if ($request->has('to_date')) {
            $query->where('created_at', '<=', $request->to_date);
        }

        $posts = $query->with('author:id,name')
            ->paginate(15);

        return response()->json($posts);
    }
}

// Usage examples:
// GET /api/posts?status=published
// GET /api/posts?author_id=5&status=published
// GET /api/posts?from_date=2024-01-01&to_date=2024-12-31
</pre>

Advanced Filtering Patterns

1. Multiple Values for Single Field

Allow filtering by multiple values using comma-separated lists or array syntax:

<?php
public function index(Request $request): JsonResponse
{
    $query = Post::query();

    // Filter by multiple statuses
    if ($request->has('status')) {
        $statuses = explode(',', $request->status);
        $query->whereIn('status', $statuses);
    }

    // Alternative: array syntax
    if ($request->has('categories')) {
        $categories = $request->input('categories', []);
        $query->whereHas('categories', function ($q) use ($categories) {
            $q->whereIn('id', $categories);
        });
    }

    $posts = $query->paginate(15);
    return response()->json($posts);
}

// Usage:
// GET /api/posts?status=published,draft
// GET /api/posts?categories[]=1&categories[]=2&categories[]=3
</pre>

2. Comparison Operators

Support various comparison operators for numeric and date fields:

<?php
public function index(Request $request): JsonResponse
{
    $query = Post::query();

    // Filter by views with operator support
    if ($request->has('views')) {
        $operator = $request->input('views_operator', '=');
        $value = $request->input('views');

        $allowedOperators = ['=', '!=', '>', '>=', '<', '<='];
        if (in_array($operator, $allowedOperators)) {
            $query->where('views', $operator, $value);
        }
    }

    // Alternative: embedded operator syntax
    if ($request->has('rating')) {
        $this->applyComparison($query, 'rating', $request->rating);
    }

    $posts = $query->paginate(15);
    return response()->json($posts);
}

protected function applyComparison($query, $field, $value)
{
    // Parse syntax like "gt:4.5", "lte:100", "eq:50"
    if (preg_match('/^(gt|gte|lt|lte|eq|ne):(.+)$/', $value, $matches)) {
        $operator = match ($matches[1]) {
            'gt' => '>',
            'gte' => '>=',
            'lt' => '<',
            'lte' => '<=',
            'eq' => '=',
            'ne' => '!=',
        };
        $query->where($field, $operator, $matches[2]);
    } else {
        $query->where($field, $value);
    }
}

// Usage:
// GET /api/posts?views=1000&views_operator=>=
// GET /api/posts?rating=gt:4.5
// GET /api/posts?price=lte:99.99
</pre>

3. Relationship Filtering

Filter based on related model attributes:

<?php
public function index(Request $request): JsonResponse
{
    $query = Post::with('author', 'tags');

    // Filter by author name
    if ($request->filled('author_name')) {
        $query->whereHas('author', function ($q) use ($request) {
            $q->where('name', 'like', '%' . $request->author_name . '%');
        });
    }

    // Filter by tag names
    if ($request->filled('tags')) {
        $tags = explode(',', $request->tags);
        $query->whereHas('tags', function ($q) use ($tags) {
            $q->whereIn('name', $tags);
        });
    }

    // Filter by minimum comment count
    if ($request->has('min_comments')) {
        $query->withCount('comments')
            ->having('comments_count', '>=', $request->min_comments);
    }

    $posts = $query->paginate(15);
    return response()->json($posts);
}

// Usage:
// GET /api/posts?author_name=John
// GET /api/posts?tags=laravel,php
// GET /api/posts?min_comments=10
</pre>

Pro Tip: Always validate and sanitize filter parameters to prevent SQL injection and ensure only allowed fields can be filtered.

Sorting Implementation

Enable flexible sorting with query parameters:

<?php
public function index(Request $request): JsonResponse
{
    $request->validate([
        'sort_by' => 'string|in:created_at,title,views,rating',
        'sort_direction' => 'string|in:asc,desc',
    ]);

    $query = Post::query();

    // Apply sorting
    $sortBy = $request->input('sort_by', 'created_at');
    $sortDirection = $request->input('sort_direction', 'desc');

    $query->orderBy($sortBy, $sortDirection);

    $posts = $query->paginate(15);
    return response()->json($posts);
}

// Usage:
// GET /api/posts?sort_by=views&sort_direction=desc
// GET /api/posts?sort_by=title&sort_direction=asc
</pre>

Multi-Column Sorting

Support sorting by multiple columns:

<?php
public function index(Request $request): JsonResponse
{
    $query = Post::query();

    // Sort syntax: "sort=-views,created_at" (minus for descending)
    if ($request->has('sort')) {
        $sortFields = explode(',', $request->sort);

        foreach ($sortFields as $field) {
            $direction = 'asc';

            if (str_starts_with($field, '-')) {
                $direction = 'desc';
                $field = substr($field, 1);
            }

            $allowedFields = ['id', 'created_at', 'title', 'views', 'rating'];
            if (in_array($field, $allowedFields)) {
                $query->orderBy($field, $direction);
            }
        }
    } else {
        // Default sorting
        $query->latest();
    }

    $posts = $query->paginate(15);
    return response()->json($posts);
}

// Usage:
// GET /api/posts?sort=-views,created_at  (desc views, asc created_at)
// GET /api/posts?sort=-rating,-created_at (desc both)
</pre>

Relationship Sorting

Sort by related model fields:

<?php
public function index(Request $request): JsonResponse
{
    $query = Post::query();

    // Sort by author name
    if ($request->input('sort_by') === 'author_name') {
        $query->join('users', 'posts.author_id', '=', 'users.id')
            ->orderBy('users.name', $request->input('sort_direction', 'asc'))
            ->select('posts.*');
    } else {
        $query->orderBy(
            $request->input('sort_by', 'created_at'),
            $request->input('sort_direction', 'desc')
        );
    }

    $posts = $query->with('author')->paginate(15);
    return response()->json($posts);
}

// Usage:
// GET /api/posts?sort_by=author_name&sort_direction=asc
</pre>

Search Implementation

Basic Search

Implement simple text search across multiple fields:

<?php
public function index(Request $request): JsonResponse
{
    $request->validate([
        'search' => 'string|max:255',
    ]);

    $query = Post::query();

    if ($request->filled('search')) {
        $search = $request->search;

        $query->where(function ($q) use ($search) {
            $q->where('title', 'like', "%{$search}%")
              ->orWhere('content', 'like', "%{$search}%")
              ->orWhere('excerpt', 'like', "%{$search}%");
        });
    }

    $posts = $query->with('author:id,name')
        ->paginate(15);

    return response()->json($posts);
}

// Usage:
// GET /api/posts?search=Laravel
</pre>

Search with Relationships

Extend search to related models:

<?php
public function index(Request $request): JsonResponse
{
    $query = Post::query();

    if ($request->filled('search')) {
        $search = $request->search;

        $query->where(function ($q) use ($search) {
            // Search in post fields
            $q->where('title', 'like', "%{$search}%")
              ->orWhere('content', 'like', "%{$search}%")

              // Search in author name
              ->orWhereHas('author', function ($q) use ($search) {
                  $q->where('name', 'like', "%{$search}%");
              })

              // Search in tags
              ->orWhereHas('tags', function ($q) use ($search) {
                  $q->where('name', 'like', "%{$search}%");
              });
        });
    }

    $posts = $query->with('author', 'tags')
        ->distinct()
        ->paginate(15);

    return response()->json($posts);
}

// Usage:
// GET /api/posts?search=John  (finds posts by author John or containing "John")
</pre>

Warning: The LIKE operator with leading wildcards (%search%) cannot use indexes efficiently. For large datasets, consider implementing full-text search using MySQL FULLTEXT indexes or dedicated search engines like Elasticsearch, Meilisearch, or Algolia.

Full-Text Search (MySQL)

For better performance on large datasets, use MySQL full-text search:

<?php
// Migration
Schema::table('posts', function (Blueprint $table) {
    $table->fullText(['title', 'content', 'excerpt']);
});

// Controller
public function index(Request $request): JsonResponse
{
    $query = Post::query();

    if ($request->filled('search')) {
        $search = $request->search;

        // Full-text search (much faster for large datasets)
        $query->whereRaw(
            'MATCH(title, content, excerpt) AGAINST(? IN BOOLEAN MODE)',
            [$search]
        );
    }

    $posts = $query->with('author:id,name')
        ->paginate(15);

    return response()->json($posts);
}

// Usage:
// GET /api/posts?search=Laravel+tutorial
</pre>

Laravel Scout for Search

For advanced search capabilities, use Laravel Scout with Meilisearch or Algolia:

<?php
// Install Scout and driver
// composer require laravel/scout
// composer require meilisearch/meilisearch-php

// Make model searchable
use Laravel\Scout\Searchable;

class Post extends Model
{
    use Searchable;

    public function toSearchableArray(): array
    {
        return [
            'id' => $this->id,
            'title' => $this->title,
            'content' => $this->content,
            'excerpt' => $this->excerpt,
            'author_name' => $this->author->name,
            'tags' => $this->tags->pluck('name'),
        ];
    }
}

// Controller with Scout
public function index(Request $request): JsonResponse
{
    if ($request->filled('search')) {
        // Scout search (typo-tolerant, fast, relevant)
        $posts = Post::search($request->search)
            ->query(fn ($query) => $query->with('author'))
            ->paginate(15);
    } else {
        $posts = Post::with('author')->paginate(15);
    }

    return response()->json($posts);
}
</pre>

Spatie Query Builder Package

The Spatie Laravel Query Builder package provides a clean, standardized approach to filtering, sorting, and including relationships:

<?php
// Install package
// composer require spatie/laravel-query-builder

use Spatie\QueryBuilder\QueryBuilder;
use Spatie\QueryBuilder\AllowedFilter;
use Spatie\QueryBuilder\AllowedSort;

class PostController extends Controller
{
    public function index(Request $request): JsonResponse
    {
        $posts = QueryBuilder::for(Post::class)
            // Allowed filters
            ->allowedFilters([
                'status',
                'author_id',
                AllowedFilter::exact('id'),
                AllowedFilter::partial('title'),
                AllowedFilter::scope('published_after'),
            ])
            // Allowed sorts
            ->allowedSorts([
                'created_at',
                'title',
                'views',
                AllowedSort::field('author', 'author_id'),
            ])
            // Allowed includes (eager loading)
            ->allowedIncludes(['author', 'tags', 'comments'])
            // Default sort
            ->defaultSort('-created_at')
            ->paginate(15);

        return response()->json($posts);
    }
}

// Usage:
// GET /api/posts?filter[status]=published&filter[title]=Laravel
// GET /api/posts?sort=-views,created_at
// GET /api/posts?include=author,tags
// GET /api/posts?filter[status]=published&sort=-created_at&include=author
</pre>

Custom Filters with Spatie

Create custom filter logic:

<?php
// app/Filters/PopularFilter.php
namespace App\Filters;

use Illuminate\Database\Eloquent\Builder;
use Spatie\QueryBuilder\Filters\Filter;

class PopularFilter implements Filter
{
    public function __invoke(Builder $query, $value, string $property): void
    {
        $query->where('views', '>=', 1000)
            ->where('rating', '>=', 4.5);
    }
}

// Controller
QueryBuilder::for(Post::class)
    ->allowedFilters([
        'status',
        AllowedFilter::custom('popular', new PopularFilter()),
    ])
    ->paginate(15);

// Usage:
// GET /api/posts?filter[popular]=true
</pre>

Request Validation for Query Parameters

Always validate query parameters to ensure data integrity:

<?php
// app/Http/Requests/PostIndexRequest.php

namespace App\Http\Requests;

use Illuminate\Foundation\Http\FormRequest;

class PostIndexRequest extends FormRequest
{
    public function authorize(): bool
    {
        return true;
    }

    public function rules(): array
    {
        return [
            // Filtering
            'status' => 'string|in:draft,published,archived',
            'author_id' => 'integer|exists:users,id',
            'from_date' => 'date',
            'to_date' => 'date|after_or_equal:from_date',
            'tags' => 'string',

            // Sorting
            'sort_by' => 'string|in:created_at,title,views,rating',
            'sort_direction' => 'string|in:asc,desc',

            // Search
            'search' => 'string|max:255',

            // Pagination
            'per_page' => 'integer|min:1|max:100',
        ];
    }
}

// Controller
public function index(PostIndexRequest $request): JsonResponse
{
    // All parameters are now validated
    $query = Post::query();

    // Your filtering, sorting, searching logic here...

    return response()->json($query->paginate($request->input('per_page', 15)));
}
</pre>

Performance Optimization

1. Database Indexes

<?php
// Migration
Schema::table('posts', function (Blueprint $table) {
    // Indexes for common filters
    $table->index('status');
    $table->index('author_id');
    $table->index('created_at');

    // Compound indexes for combined filters
    $table->index(['status', 'created_at']);
    $table->index(['author_id', 'status']);

    // Full-text index for search
    $table->fullText(['title', 'content']);
});
</pre>

2. Select Only Needed Columns

<?php
// Inefficient: Selects all columns
$posts = Post::where('status', 'published')->get();

// Efficient: Selects only needed columns
$posts = Post::select(['id', 'title', 'excerpt', 'author_id', 'created_at'])
    ->where('status', 'published')
    ->get();
</pre>

3. Eager Load Relationships

<?php
// N+1 problem
$posts = Post::where('status', 'published')->get();
// Each post triggers separate author query

// Solution: Eager loading
$posts = Post::with('author:id,name', 'tags:id,name')
    ->where('status', 'published')
    ->get();
</pre>

Practice Exercise:

Implement a comprehensive filtering system for a Product API that supports price ranges, categories, brands, ratings, and availability status
Build a multi-column sorting system that allows sorting by up to 3 different fields simultaneously
Create a search feature that searches across products, categories, and brands with relevance scoring
Integrate Laravel Scout with Meilisearch for typo-tolerant, fast search on a blog API
Implement the Spatie Query Builder package for a User API with custom filters for account age, activity level, and subscription status

Best Practices

Query Features Best Practices:

Whitelist allowed fields: Only allow filtering/sorting on specific, safe columns
Validate all inputs: Use Form Requests to validate query parameters
Add database indexes: Index all filterable and sortable columns
Use eager loading: Avoid N+1 queries with with()
Limit search scope: Don't search across too many fields or use full-text search
Document your API: Clearly document available filters, sorts, and search capabilities
Set reasonable limits: Limit search query length and result count
Consider caching: Cache frequently used filter combinations

Summary

In this lesson, you've mastered implementing filtering, sorting, and searching capabilities in Laravel APIs. You now understand how to build flexible filtering systems with multiple operators, implement single and multi-column sorting, create powerful search features with LIKE queries and full-text indexes, integrate Laravel Scout for advanced search, and use the Spatie Query Builder package for standardized querying. These query features are essential for creating flexible, performant APIs that empower clients to efficiently access exactly the data they need.