Postgres Search Bundle Laravel Package

Getting Started

Do not composer require this into a Laravel app. The package is a Symfony2 bundle built on the Doctrine 2 Symfony bridge; it registers a compiler pass, ships service definitions in Symfony DI XML/YAML, and maps entity fields via Doctrine annotations/loadClassMetadata listeners. None of that resolves in Laravel's container, and pulling in Doctrine + the Symfony2 bridge to satisfy it is a worse trade than writing the ~150 lines of SQL it wraps. Treat the repository as reference source, not a dependency.

Where to look first, in this order:

1. Resources/config/ — the service definitions tell you exactly which classes do the work and what the configurable knobs (dictionary/regconfig, weighted fields) actually map to.
2. The Doctrine mapping/listener directory — this is where tsvector columns and setweight expressions are declared. It is the spec for the generated column you will write.
3. The query builder helpers — the @@ operator construction, prefix (:*) and phrase (<->) handling, and ts_rank/ts_rank_cd ordering. This is the part worth copying conceptually.

First use case to reproduce: a single-entity, weighted keyword search. Migration first:

// Postgres 12+; note the explicit regconfig — required for immutability
DB::statement(<<<'SQL'
ALTER TABLE articles ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')),  'B')
) STORED;
SQL);

DB::statement('CREATE INDEX articles_search_idx ON articles USING GIN (search_vector)');

Then a query scope:

public function scopeSearch(Builder $q, string $term): Builder
{
    return $q->whereRaw('search_vector @@ websearch_to_tsquery(?, ?)', ['english', $term])
             ->orderByRaw('ts_rank_cd(search_vector, websearch_to_tsquery(?, ?)) DESC', ['english', $term]);
}

That is the day-to-day leverage: you read how the bundle did weighted ranking, then you own a scope, a generated column, and a GIN index with zero external infrastructure.

Implementation Patterns

Weighted columns (A–D). The bundle's core value is the setweight composition shown above. Convention that works well: A = title/name, B = subtitle/tags, C = body, D = fallback blob. Keep the weights in one place (a migration or a Searchable trait) so schema and query stay in sync.

Generated column vs. trigger. Prefer GENERATED ALWAYS AS ... STORED — it is cheaper on writes and cannot drift. Drop to a BEFORE INSERT OR UPDATE trigger only when you need coalesce over joined data or a stable-but-not-immutable function. This is precisely the decision the bundle hid behind Doctrine lifecycle callbacks; in Laravel you make it explicitly in a migration.

Query construction: pick the right parser.

Prefix matching (foo:*) only exists on to_tsquery — which means you must sanitize lexemes yourself (preg_replace('/[^\pL\pN]+/u', ' ', $term)) and append :*. The bundle's "prefix helper" is exactly this, and it is the one helper worth porting verbatim.

Phrase matching. to_tsquery('english', 'foo <-> bar') enforces adjacency; <N> allows distance. Reach for it for "exact product name" search; do not use it for the general search box.

Wrap the backend behind an interface. This is the pattern that makes the whole exercise safe:

interface ArticleSearch { public function search(string $term, int $perPage = 20): LengthAwarePaginator; }

Bind a Postgres implementation now, swap in Scout/Meilisearch later without touching controllers. This is the single most valuable thing you can take from an unmaintained package: the awareness that the backend is replaceable.

Ranking and pagination. Filter with @@ first (GIN-indexable), then ORDER BY ts_rank_cd(...). Never order by rank without a filter — the planner will seq-scan. Paginate with Laravel's paginator; ts_rank_cd returns real, so tie-break on id for stable pages.

Multi-field / multi-entity. For related content, denormalize into the searchable row (or a dedicated search_documents table) rather than joining at query time — tsvector cannot span joins in an indexed way.

Gotchas and Tips

• regconfig immutability is the #1 trap. to_tsvector(body) is stable, not immutable, and Postgres will reject it in a generated column. You must write to_tsvector('english', body). The bundle sidestepped this with triggers; your migration will fail loudly until you qualify the config.
• Config mismatch yields silent zero results. If the column is built with 'english' and you query to_tsvector('simple', ...), nothing matches and nothing errors. Pin the config in one constant and test it.
• Never concatenate user input into tsquery. to_tsquery("foo (bar") throws syntax error in tsquery and takes down the request. Use websearch_to_tsquery for raw input, parameter binding always, and add a fuzz test with malformed strings ('', '*', '"', 'a & ').
• ts_rank normalization flags matter. ts_rank(v, q, 32) normalizes by 1/(rank+1); without it, long documents outrank short ones. Default (0) is often fine for ts_rank_cd with weight classes — but measure with a fixed query corpus before tuning.
• work_mem is your latency knob. GIN scans on large result sets spill to disk. Set per-query: DB::statement("SET LOCAL work_mem = '64MB'") inside the search transaction, or tune globally.
• GIN index bloat. High-churn tables need VACUUM/REINDEX hygiene; monitor pg_stat_user_indexes (idx_scan) to confirm the index is actually used and not dead weight.
• Verify plans, don't assume. EXPLAIN (ANALYZE, BUFFERS) SELECT ... WHERE search_vector @@ ... should show Bitmap Index Scan on articles_search_idx. If you see Seq Scan, your expression is not matching the index (usually a config mismatch or an OR across columns).
• Relevance regressions are invisible. Build a small CI fixture: 10–20 queries with expected top-3 IDs. Postgres major upgrades and dictionary changes silently shift rankings; this is the only cheap guard.
• Managed Postgres caveats. Extensions like unaccent (used for accent folding) may be unavailable; websearch_to_tsquery requires PG 11+, generated columns PG 12+. Confirm the provider's version before committing to the design.
• If you are actually on Symfony 2. The extension points are the bundle's dictionary/config node (per-field regconfig) and its query helper service — but Symfony 2 has been EOL since 2018, so this only applies to a frozen legacy app where the bundle is already vendored. Copy the SQL, keep the container untouched.
• Typos and faceting are out of scope. tsquery does no fuzzy matching; pg_trgm adds similarity but not ranking quality, and neither gives you facets or synonyms. If the product asks for those, the honest answer is a dedicated engine — a decision this bundle neither makes nor helps with.