featured image
Web Developmentcategory

Building a Search System with Filtering and Pagination

Learn how to build a scalable search system in Laravel and Vue.js that supports multi-model searching, filtering, pagination, and performance optimizations.

Published

Mon Oct 27 2025

Technologies Used

Laravel Vue.js Inertia.js PostgreSQL SQL PHP
Intermediate 44 minutes

When a Single LIKE Query Isn’t Enough

The naive search implementation is a trap:

public function search(Request $request)
{
    $query = $request->get('q');
    $results = Post::where('title', 'LIKE', "%{$query}%")
        ->orWhere('content', 'LIKE', "%{$query}%")
        ->get(); // No pagination, no limit
    
    return view('search', ['results' => $results]);
}

On a small dataset this works fine. Then you have 100,000 records, the query runs a full table scan, loads everything into memory, and the request times out. Add the fact that you can’t filter by type, can’t sort, can’t search across multiple models, and you have a feature that breaks in production and can’t be extended.

MyKpopLists has six content types — groups, idols, albums, songs, variety shows, and users — and users expect to search across all of them from a single input. This tutorial covers the architecture that makes that work: case-insensitive multi-model queries, per-category limits, eager loading to prevent N+1 problems, and a debounced Vue frontend.

What to Set Up First

Knowledge you’ll need:

  • Laravel Eloquent query building, relationships, eager loading
  • Database indexes and why they matter
  • SQL LIKE queries and the LOWER() function
  • Vue 3 reactive search UI and debouncing

Database indexes for search columns:

Schema::table('groups', function (Blueprint $table) {
    $table->index('name');
});

Schema::table('idols', function (Blueprint $table) {
    $table->index('stage_name');
    $table->index('birth_name');
});

Schema::table('songs', function (Blueprint $table) {
    $table->index('title');
});

Without indexes, searching 100,000 songs requires a full table scan. With a B-tree index, the database can find matches in milliseconds. One important caveat: indexes don’t help with leading wildcards (%term). If you need full prefix-based search, look at Laravel Scout with Meilisearch or Algolia.

The Architecture: Multiple Focused Queries, Not One Giant JOIN

The search controller runs separate queries against each model, limits results to 5 per category, transforms everything to a unified format, and concatenates the results. This is faster and more maintainable than a complex JOIN operation, and it gives each content type its own result section in the UI.

<?php

namespace App\Http\Controllers;

use App\Models\Album;
use App\Models\Group;
use App\Models\Idol;
use App\Models\Song;
use App\Models\User;
use App\Models\VarietyShow;
use Illuminate\Http\Request;

class SearchController extends Controller
{
    public function search(Request $request)
    {
        $query = $request->get('q', '');
        
        if (empty(trim($query))) {
            return response()->json(['results' => [], 'total' => 0]);
        }

        // Normalize search term: lowercase for case-insensitive search
        $searchTerm = '%' . strtolower($query) . '%';
        
        $results = $this->searchAllModels($searchTerm);

        return response()->json([
            'results' => $results->values(),
            'total' => $results->count(),
            'query' => $query
        ]);
    }
}

The early return for empty queries matters. Every database query skipped is resources saved, and searching for an empty string would return every row in every table.

Querying Each Model and Transforming to a Unified Format

protected function searchAllModels(string $searchTerm)
{
    // Search Groups
    $groups = Group::whereRaw('LOWER(name) LIKE ?', [$searchTerm])
        ->limit(5)
        ->get()
        ->map(function ($group) {
            return [
                'id' => $group->id,
                'type' => 'group',
                'title' => $group->name,
                'image' => $group->image_url,
                'url' => route('groups.show', $group),
                'description' => $group->description 
                    ? substr($group->description, 0, 100) . '...' 
                    : null,
            ];
        });

    // Search Idols — note the eager loading to prevent N+1
    $idols = Idol::where(function ($q) use ($searchTerm) {
            $q->whereRaw('LOWER(birth_name) LIKE ?', [$searchTerm])
              ->orWhereRaw('LOWER(stage_name) LIKE ?', [$searchTerm]);
        })
        ->with('groups') // Load all groups in a single query
        ->limit(5)
        ->get()
        ->map(function ($idol) {
            return [
                'id' => $idol->id,
                'type' => 'idol',
                'title' => $idol->stage_name ?: $idol->birth_name,
                'subtitle' => $idol->groups->pluck('name')->join(', '),
                'image' => $idol->image_url,
                'url' => route('idols.show', $idol),
            ];
        });

    // Search Songs — nested eager loading for the album and artist relationship
    $songs = Song::whereRaw('LOWER(title) LIKE ?', [$searchTerm])
        ->with(['album.group', 'album.idol'])
        ->limit(5)
        ->get()
        ->map(function ($song) {
            $artist = $song->artist ?: $song->album?->artist;
            return [
                'id' => $song->id,
                'type' => 'song',
                'title' => $song->title,
                'subtitle' => $artist ? ($artist->stage_name ?? $artist->name) : null,
                'image' => $song->album?->cover_image_url,
                'url' => route('songs.show', $song),
            ];
        });

    // Users are only searchable when authenticated
    $users = collect();
    if (auth()->check()) {
        $users = User::where(function ($q) use ($searchTerm) {
                $q->whereRaw('LOWER(name) LIKE ?', [$searchTerm])
                  ->orWhereRaw('LOWER(username) LIKE ?', [$searchTerm]);
            })
            ->limit(5)
            ->get()
            ->map(function ($user) {
                return [
                    'id' => $user->id,
                    'type' => 'user',
                    'title' => $user->name,
                    'subtitle' => '@' . $user->username,
                    'image' => $user->profile_picture,
                    'url' => route('users.show', $user->username),
                ];
            });
    }

    return $groups
        ->concat($idols)
        ->concat($albums)
        ->concat($songs)
        ->concat($varietyShows)
        ->concat($users)
        ->take(25);
}

The whereRaw('LOWER(name) LIKE ?', [$searchTerm]) pattern is database-agnostic. MySQL is case-insensitive by default, but PostgreSQL is case-sensitive. The explicit LOWER() works the same way on both. The ? placeholder is parameterized — Laravel escapes the value, preventing SQL injection. Never concatenate user input directly into a raw query string.

N+1 Prevention With Eager Loading

This is the most common performance mistake in search implementations. Without eager loading:

// BAD: N+1 queries
$idols = Idol::whereRaw('LOWER(stage_name) LIKE ?', [$searchTerm])->get();

foreach ($idols as $idol) {
    echo $idol->groups->pluck('name')->join(', ');
    // Fires a separate query for EACH idol's groups
}
// 50 idols = 51 queries

With eager loading:

// GOOD: 2 queries total
$idols = Idol::whereRaw('LOWER(stage_name) LIKE ?', [$searchTerm])
    ->with('groups')
    ->get();
// Query 1: SELECT * FROM idols WHERE...
// Query 2: SELECT * FROM groups WHERE idol_id IN (1,2,3,...,50)
// All groups attached in memory — no additional queries during the map

For albums, the scopeWithArtist custom scope keeps the controller clean:

// app/Models/Album.php
public function scopeWithArtist($query)
{
    return $query->with(['group', 'idol']);
}

public function getArtistAttribute()
{
    return $this->group ?: $this->idol;
}

Adding Type Filtering and Sorting

When users want to filter to a specific content type and see paginated results:

public function search(Request $request)
{
    $query = $request->get('q', '');
    $type = $request->get('type');
    $sort = $request->get('sort', 'relevance');
    
    if (strlen(trim($query)) < 2) {
        return response()->json([
            'results' => [],
            'message' => 'Please enter at least 2 characters',
        ]);
    }

    $searchTerm = '%' . strtolower($query) . '%';
    
    if ($type) {
        $results = $this->searchSingleType($type, $searchTerm, $sort);
    } else {
        $results = $this->searchAllModels($searchTerm);
    }

    return response()->json([
        'results' => $results->values(),
        'total' => $results->count(),
        'query' => $query,
        'filters' => ['type' => $type, 'sort' => $sort]
    ]);
}

protected function searchSingleType(string $type, string $searchTerm, string $sort)
{
    $query = match($type) {
        'group' => Group::whereRaw('LOWER(name) LIKE ?', [$searchTerm]),
        'idol' => Idol::where(function ($q) use ($searchTerm) {
            $q->whereRaw('LOWER(birth_name) LIKE ?', [$searchTerm])
              ->orWhereRaw('LOWER(stage_name) LIKE ?', [$searchTerm]);
        })->with('groups'),
        'song' => Song::whereRaw('LOWER(title) LIKE ?', [$searchTerm])
            ->with(['album.group', 'album.idol']),
        default => throw new \InvalidArgumentException("Invalid type: {$type}"),
    };

    $query = match($sort) {
        'name' => $query->orderBy('name'),
        'date' => $query->latest(),
        default => $query,
    };

    return $query->paginate(20);
}

The distinction between limit() and paginate() matters. limit() is faster — it caps the rows returned without counting the total. Use it for multi-model overview searches where a total count isn’t needed. paginate() fires an additional COUNT query to support page navigation. Use it when a user has filtered to a single type and needs to browse through many results.

The Vue Frontend: Debouncing and Grouped Display

<script setup lang="ts">
import { ref, watch, computed } from 'vue'
import { useDebounceFn } from '@vueuse/core'

const searchQuery = ref('')
const results = ref([])
const isSearching = ref(false)
const selectedType = ref<string | null>(null)

const performSearch = useDebounceFn(async () => {
  if (searchQuery.value.length < 2) {
    results.value = []
    return
  }

  isSearching.value = true

  try {
    const response = await fetch(
      `/search?q=${encodeURIComponent(searchQuery.value)}` +
      (selectedType.value ? `&type=${selectedType.value}` : '')
    )
    const data = await response.json()
    results.value = data.results
  } catch (error) {
    console.error('Search failed:', error)
  } finally {
    isSearching.value = false
  }
}, 300)

watch(searchQuery, () => { performSearch() })

const groupedResults = computed(() => {
  const grouped: Record<string, any[]> = {}
  results.value.forEach((result: any) => {
    if (!grouped[result.type]) grouped[result.type] = []
    grouped[result.type].push(result)
  })
  return grouped
})

const typeLabels: Record<string, string> = {
  group: 'Groups',
  idol: 'Idols',
  album: 'Albums',
  song: 'Songs',
  variety_show: 'Variety Shows',
  user: 'Users',
}
</script>

The 300ms debounce means the API is only called after the user pauses typing. Without it, every keystroke fires a request — at typical typing speed, that’s 5-10 requests per second for a single search. The minimum 2-character requirement also filters out single-character searches that would return enormous result sets.

Special Characters That Break LIKE Queries

The % and _ characters have special meaning in SQL LIKE patterns. A user searching for “100%” would have the % treated as a wildcard, matching anything. Escape them before constructing the search term:

protected function escapeLikeValue(string $value): string
{
    $value = str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], $value);
    return '%' . strtolower($value) . '%';
}

In MyKpopLists, this search system handles 100,000+ records and searches across six models in under 100ms. The combination of per-category limits (5 results each), eager loading, and database indexes keeps memory usage constant regardless of dataset size. Eager loading alone reduces query count by about 95% compared to the naive loop approach.

For more sophisticated search needs — full-text ranking, fuzzy matching, stemming — Laravel Scout with Meilisearch adds those capabilities without changing the controller architecture significantly. The search controller calls methods, the indexing driver handles how those queries are actually executed.

We respect your privacy.

Newer
Your App Works — Until the User Closes It: Persisting Chat Sessions Without AsyncStorage
Older
Implementing Real-Time Notifications with Laravel Reverb
← View All Tutorials

Related Projects
    Ask me anything!