Search

The search box of DataTables and list pages ignores Turkish letter variants and letter case, and stays fast on large tables. This page explains how it works and how to set it up.

What it gives you:

• When the user types "ayse", "Ayşe" is found; "ismail isik" finds "İsmail Işık".
• The default search uses an index; even on millions of rows, it doesn't read the table from start to finish.
• DataTable and WithListing share the same infrastructure: the same field definition (SearchField), the same driver, and the same rows for the same term.
• If you need typo tolerance, move to a search engine (Meilisearch, Typesense) with your own driver; your table code stays the same.

Note

The search infrastructure is part of the PRO package (acunsoft/acun-ui-pro), which comes installed with Acun themes.

Quick example

Add the search column in a migration:

Schema::table('customers', function (Blueprint $table) {
    $table->searchColumn('email');   // the email_search column and its index
});

Then make the field searchable. In a DataTable, mark the column; on a list page, declare the field:

// DataTable
Column::make('Email', 'email')->searchable(),

// WithListing
protected function searchFields(): array
{
    return [SearchField::make('email')];
}

The search runs on the email_search column, which the database generates from the email column. It's a generated column: the database computes its value and keeps it up to date. The default search is a prefix search, and it uses the index.

Why a generated search column?

The search runs on a search-ready copy of the data. The source column stays as it is:

name            name_search
Ayşe Yılmaz     ayse yilmaz
İsmail IŞIK     ismail isik

Each layer does one job:

• The source column stores: It keeps the data as is, in UTF-8 ("Ayşe Yılmaz"). Display and sorting use it.
• The search column folds: It reduces Turkish letters and letter case to a single form ("ayse yilmaz").
• A binary collation compares: Since the value is already folded, it's compared byte by byte; no language rules are needed.
• The index speeds it up: A prefix search walks the index instead of reading the table from start to finish.

Because the database generates the column, insert, raw update, imports and Model::query()->update() also keep it up to date automatically. There's no filling in PHP, no model event and no backfill. Writing LOWER(REPLACE(column…)) LIKE '%…%' at query time, on the other hand, reads every row on each search and can't use an index.

The folding rules are applied by Acun\Ui\Search\Normalizer::fold() in PHP and Normalizer::expression() in SQL. Both give the same result:

1. İ, I, ı → i · Ş, ş → s · Ç, ç → c · Ğ, ğ → g · Ö, ö → o · Ü, ü → u. This step runs before lowercasing.
2. The value is lowercased. MySQL, MariaDB, PostgreSQL and SQL Server also lowercase other letters such as É and Ä; SQLite lowercases only A–Z.
3. Tabs and line breaks become spaces, runs of spaces collapse into one, and leading and trailing spaces are removed. The SQL side collapses runs of up to 32 spaces.

So "ayse" finds "Ayşe", "ali" finds "ALİ" and "ALI", "ismail isik" finds "İsmail Işık", and "ÇAĞRI" finds "çağrı". Turkish search isn't a separate mode; every mode works on these folded values.

Typos

Folding only reduces letters and case to one form: "aysse" does not find "Ayşe". The database driver has no fuzzy, similarity or phonetic search. If you need typo tolerance and relevance ranking, write your own driver for a search engine (see Drivers); your table code stays the same.

Adding a search column

searchColumn() adds the search column and its index in a migration:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('orders', function (Blueprint $table) {
            $table->searchColumn('number');                   // number_search + index
            $table->searchColumn('customer_email');           // customer_email_search + index
            $table->searchColumn('customer_name', index: false);

            // Multiple sources: joined with a single space, NULL counts as empty; the name is required.
            $table->searchColumn(['customer_name', 'customer_email'], 'customer_search');
        });
    }

    public function down(): void
    {
        Schema::table('orders', function (Blueprint $table) {
            $table->dropSearchColumn('number_search');
            $table->dropSearchColumn('customer_email_search');
            $table->dropSearchColumn('customer_name_search', index: false);
            $table->dropSearchColumn('customer_search');
        });
    }
};

The acun-ui.search.suffix setting changes the _search suffix, and acun-ui.search.length the default length. Values longer than the length keep their leading characters.

dropSearchColumn() removes the column together with its index. Pass it the same index and fullText values you used when creating the column.

Column names can follow your own conventions. For example, in a project with Turkish naming: $table->searchColumn('name', 'ad_arama').

What gets created on each database

• SQLite: a VIRTUAL column, the default BINARY collation and a regular index. fullText has no effect; the FullText mode falls back to searching anywhere in the value.
• MySQL / MariaDB: a VIRTUAL column; STORED with fullText: true. The collation is utf8mb4_bin. A regular index; with length: 0, the first 191 characters are indexed. fullText: true adds a FULLTEXT index, which requires a STORED column.
• PostgreSQL: a STORED column, the "C" collation and a regular btree index. fullText: true adds a pg_trgm GIN index (gin_trgm_ops). The pg_trgm extension must be installed in an earlier migration; otherwise the migration throws a descriptive error.
• SQL Server: a PERSISTED computed column, the Latin1_General_100_BIN2 collation and a regular index. fullText has no effect.

The SQL generated on MySQL (abbreviated):

alter table `orders` add `customer_email_search` varchar(255) collate 'utf8mb4_bin'
    as (SUBSTR(TRIM(REPLACE(… LOWER(REPLACE(REPLACE(`customer_email`, 'İ', 'i'), 'I', 'i') …) …)), 1, 255));
alter table `orders` add index `orders_customer_email_search_index`(`customer_email_search`);

On PostgreSQL:

alter table "orders" add column "customer_email_search" varchar(255) collate "C" null
    generated always as (SUBSTR(TRIM(REPLACE(… LOWER(REPLACE("customer_email", 'İ', 'i') …) …)), 1, 255)) stored;
create index "orders_customer_email_search_index" on "orders" ("customer_email_search");

The expression uses only deterministic functions, which always give the same result. On PostgreSQL, these are IMMUTABLE functions: replace, lower, trim, substr, coalesce, ||. concat_ws isn't used because it's STABLE.

Large existing tables

The cost of adding a column to a large existing table depends on the database:

• MySQL / MariaDB: adding a VIRTUAL column is an instant ALTER; the index is built online. With fullText: true, the STORED column and the FULLTEXT index rewrite the table. On millions of rows, schedule it for an off-peak hour or use an online schema tool such as pt-online-schema-change or gh-ost.
• PostgreSQL: adding a STORED column rewrites the table and locks it while doing so. To build the index with CREATE INDEX CONCURRENTLY in a separate migration, pass index: false and write the statement yourself.
• SQLite: a VIRTUAL column is instant.

Things to watch out for

• Changes that rebuild the table on SQLite. ->change(), or adding and dropping foreign keys, makes Laravel rebuild the table on SQLite. In the process, the generated column becomes a plain column and stops tracking its source. In local and testing environments, the package reports this with an error. The fix: in a new migration, two separate Schema::table() calls, first dropSearchColumn(), then searchColumn(). dropColumn() and renameColumn() are safe.
• The source column's type on PostgreSQL. You can't change the type of a column that a generated column uses. First dropSearchColumn(), then the type change, and finally searchColumn() again.
• select *. Search columns come along into the model as well. If the model is serialized to JSON, add the columns to $hidden. If you use replicate(), exclude these columns; a generated column can't be written to.

Search modes

The mode decides where in the value the term may appear:

use Acun\Ui\Search\SearchField;
use Acun\Ui\Search\SearchMode;

SearchField::make('number');                                  // Prefix: the start of the value (default)
SearchField::make('national_id', SearchMode::Exact);          // the whole value
SearchField::make('customer_name', SearchMode::Contains);     // anywhere in the value
SearchField::make('customer_name', 'fulltext');               // every word, in any order

You can also write the mode by name: 'prefix', 'exact', 'contains', 'fulltext'. Every mode works on folded values.

The condition each mode produces:

• Prefix: LIKE 'term%'; GLOB 'term*' on SQLite. Uses the index.
• Exact: = 'term'. Uses the index.
• Contains: LIKE '%term%'. Doesn't use an index, except with a pg_trgm index on PostgreSQL.
• FullText: MATCH … AGAINST('+ayse* +yil*' IN BOOLEAN MODE) on MySQL and MariaDB. There, every word must start a word of the value. On other databases, each word becomes LIKE '%…%'.

Good to know:

• Prefix finds the value "ayse yilmaz" with "ayse" but not with "yilmaz". If the second word of a name field should also match, use Contains on small tables and FullText on large ones.
• On MySQL and MariaDB, FullText uses the FULLTEXT index created by fullText: true. The results depend on the server's full-text settings: minimum word length, stopwords. In an email address, @ and . are word separators. Operator characters in the term, such as +, -, * and ", also count as word separators.
• On PostgreSQL, FullText is served by the pg_trgm GIN index. On SQLite, the same condition runs without an index, which is fine for small data sets.
• Fields are combined with OR inside a single grouped where(). The query's own conditions (scopes, authorization, filters) are preserved. A single unindexed field (Contains) in the group turns the whole search into a scan; check with EXPLAIN on large tables.
• % and _ in the term are treated as literal text; so are *, ? and [ on SQLite. LIKE conditions carry an explicit escape character (ESCAPE '!'), so the behavior is the same on every database.
• An empty or whitespace-only term leaves the query unchanged.
• At most the first 200 characters and the first 10 words of the term are used (max_length, max_words).

Index notes

How a prefix search uses the index on each database:

• SQLite, GLOB: SQLite's LIKE operator is case-insensitive for ASCII. It uses an index only on NOCASE columns or with the connection-wide case_sensitive_like setting. GLOB is case-sensitive and uses an ordinary BINARY index. Since the value is already folded, that's the right choice: SEARCH orders USING INDEX orders_number_search_index (number_search>? AND number_search<?).

• MySQL: on a utf8mb4_bin column, LIKE 'term%' does a range scan on the index (type: range, key: …_search_index).

• PostgreSQL: on a column with the "C" collation, a plain btree index is used for LIKE 'term%' (Index Scan using …_search_index); text_pattern_ops isn't needed. Contains and FullText require the pg_trgm extension and a GIN index:

  DB::statement('CREATE EXTENSION IF NOT EXISTS pg_trgm');   // in an earlier migration
  
  Schema::table('customers', fn (Blueprint $table) => $table->searchColumn('name', fullText: true));
  // create index "customers_name_search_trigram" on "customers" using gin ("name_search" gin_trgm_ops)
  

  On small tables, the query planner may find a scan cheaper. Words shorter than three letters produce no trigrams.

Search in a DataTable

The search box searches the columns marked with ->searchable(). The search reads the {field}_search column; use column: to point it at a shared column:

use Acun\Ui\DataTable\Column;
use Acun\Ui\Search\SearchField;
use Acun\Ui\Search\SearchMode;

protected function columns(): array
{
    return [
        Column::make('Order no.', 'number')->searchable()->sortable(),                // number_search, prefix
        Column::make('Customer', 'customer_name')->searchable(SearchMode::Contains),  // customer_name_search
        Column::make('Contact', 'customer_name')->searchable('contains', column: 'customer_search'),
    ];
}

To also search a field that isn't displayed as a column, write your own searchFields() method. There's no need to add a hidden column:

// The email is shown inside the customer cell; the search still finds it.
protected function searchFields(): array
{
    return [...parent::searchFields(), SearchField::make('customer_email')];
}

The search box only appears when there's a field to search. SearchField can be created in two ways:

A plain string in a field list is the same as SearchField::make(): writing 'email' means SearchField::make('email').

Search on a list page

A WithListing component declares its fields with searchFields() and calls applySearch() in its query. By default, applySearch() uses $this->search and the value of searchFields():

use Acun\Ui\Search\SearchField;
use Acun\Ui\Search\SearchMode;

protected function searchFields(): array
{
    return [
        SearchField::make('name', SearchMode::Contains),
        SearchField::make('email'),
    ];
}

private function query(): Builder
{
    return $this->applySearch(Customer::query())
        ->when($this->status !== '', fn (Builder $q) => $q->where('status', $this->status))
        ->orderBy($this->sortField, $this->sortDirection);
}

A second search box (e.g. a "customer" filter) passes its own term and fields:

->when($this->customer !== '', fn (Builder $q) => $this->applySearch($q, $this->customer, [SearchField::make('users.email')]))

A component that calls applySearch() without defining searchFields() or passing fields gets a descriptive error. For the same search outside Livewire (reports, controllers): app(SearchManager::class)->driver()->apply($query, $term, $fields). For the whole list page, see the List pages guide.

Drivers

The driver decides where the search runs. DataTable and WithListing only know the Acun\Ui\Search\Contracts\SearchDriver contract:

public function apply(Builder $query, string $term, array $fields): Builder;

• database (default): The search columns described above.
• Your own driver: To use a search engine (Meilisearch, Typesense, Elasticsearch…), write a class that implements the SearchDriver interface. Your table code stays the same.

The simplest way with a search engine: get the keys of the matching records from the engine, then narrow the same query with whereKey(). Scopes, filters, authorization rules, sorting and pagination therefore stay in effect:

namespace App\Search;

use Acun\Ui\Search\Contracts\SearchDriver;
use Illuminate\Database\Eloquent\Builder;

final class ElasticSearchDriver implements SearchDriver
{
    public function __construct(private readonly ProductSearchEngine $engine) {}

    public function apply(Builder $query, string $term, array $fields): Builder
    {
        if (trim($term) === '') {
            return $query;
        }

        return $query->whereKey($this->engine->keys($term, limit: 1000));
    }
}

When you write a driver:

• The term arrives exactly as typed; the driver folds it.
• Leave the query untouched for an empty or whitespace-only term.
• Don't remove the query's own conditions; only add to them.

Give the driver a name and make it the default. A class name works in place of a name too:

// config/acun-ui.php
'search' => [
    'driver' => 'elastic',
    'drivers' => ['elastic' => App\Search\ElasticSearchDriver::class],
],

You can also pick the default driver with an environment variable: ACUN_UI_SEARCH_DRIVER=elastic. A single table or list can choose its own driver:

use Acun\Ui\Search\Contracts\SearchDriver;
use Acun\Ui\Search\SearchManager;

protected function searchDriver(): SearchDriver
{
    return app(SearchManager::class)->driver('elastic');
}

Development environment check

During development, a missing search column throws an error that tells you what to do, instead of silently returning nothing:

Acun UI search: the table "orders" has no "customer_email_search" column.
…
    Schema::table('orders', function (Blueprint $table) {
        $table->searchColumn('customer_email');
    });

In the local (local) and testing (testing) environments, the database driver checks every search column before searching: does it exist, and is it still a generated column? Joined tables and aliases are checked too. The check reads each table's schema once per request. In production, the schema is never read. Turn the check on or off with acun-ui.search.verify_columns: null for local and testing only, true or false for every environment.

Configuration

The search settings are in the search section of config/acun-ui.php (all keys: Configuration). Publish the file with php artisan vendor:publish --tag=acun-ui-config:

'search' => [
    'driver' => env('ACUN_UI_SEARCH_DRIVER', 'database'),
    'drivers' => [],              // 'name' => SearchDriver class
    'suffix' => '_search',        // searchColumn('email') → email_search
    'length' => 255,              // varchar length (0: text)
    'max_length' => 200,          // at most this many characters of the term are used
    'max_words' => 10,            // at most this many words of the term are used
    'verify_columns' => null,     // null: local and testing
],

Large table checklist

Before you ship search on a large table, check the following:

• Every searched field has a searchColumn() column; columns searched by prefix or exact match are indexed.
• The default mode is Prefix. Contains is used only on small tables, or on PostgreSQL with pg_trgm.
• A single Contains field in a search turns the whole group into a scan. For name-like fields, SearchMode::FullText with fullText: true is used on MySQL, and pg_trgm on PostgreSQL.
• The EXPLAIN plan shows the index: on MySQL type: range and key: …_search_index, or type: fulltext for full-text search; on PostgreSQL Index Scan or Bitmap Index Scan; on SQLite SEARCH … USING INDEX.
• Migrations that add STORED/FULLTEXT on MySQL or a STORED column on PostgreSQL rewrite the table. They're scheduled for an off-peak hour or run with an online schema tool; the PostgreSQL index is built with CONCURRENTLY.
• On very large tables, the total count is expensive too: consider simple or cursor for acun-ui.datatable.pagination.
• Search columns come along with select *: they're in $hidden on models serialized to JSON.
• If you need typo tolerance or relevance ranking, write your own driver for a search engine; your table code stays the same.

Learn more

• List pages: search, filters and pagination with WithListing.
• DataTable and DataTable: all options: ->searchable() and other column settings.
• Languages and translation: Turkish letter rules.