City Search with Relevance Ranking (with Laravel example)

If you’re building a city search (like an autocomplete), you can easily rank results so the most relevant matches (like exact or prefix ones) appear first.

This guide shows how to do it using a raw SQL query with PHP variables, and then the Laravel Eloquent equivalent.

---

🧾 Raw SQL Query (with PHP variable)

<?php
$query = request('query'); // Example: "del"

$sql = "
    SELECT id, name 
    FROM cities 
    WHERE name LIKE :likePattern
    ORDER BY 
        CASE
            WHEN name = :exact THEN 1
            WHEN name LIKE :prefix THEN 2
            WHEN name LIKE :contains THEN 3
            ELSE 4
        END, 
        name ASC
    LIMIT 50
";

$cities = DB::select($sql, [
    'likePattern' => '%' . $query . '%',
    'exact' => $query,
    'prefix' => $query . '%',
    'contains' => '%' . $query . '%',
]);

🔍 Explanation

• :exact → exact match (e.g., Delhi)
• :prefix → starts with query (e.g., Delmont)
• :contains → contains query (e.g., New Delhi)
• Uses bound parameters for safety (prevents SQL injection).

---

⚙️ Laravel Eloquent Version

$cities = City::select('id', 'name')
    ->where('name', 'like', '%' . request('query') . '%')
    ->orderByRaw(
        "CASE
            WHEN name = ? THEN 1
            WHEN name LIKE ? THEN 2
            WHEN name LIKE ? THEN 3
            ELSE 4
        END, name ASC",
        [
            request('query'),
            request('query') . '%',
            '%' . request('query') . '%',
        ]
    )
    ->limit(50)
    ->get();

This method returns cities ordered by relevance — exact matches first, prefix matches next, and substring matches last — perfect for a smooth, user-friendly autocomplete.

---