Doctrine Full Text Postgres Laravel Package

Getting Started

This package is not a search engine — it is thin glue that lets Doctrine speak PostgreSQL's native full-text search. Concretely it ships two things you would otherwise write yourself: a DBAL type that maps the tsvector column type, and a DQL function that compiles to the @@ match operator. Everything else (indexes, population of the vector, ranking, snippets) is still SQL you own.

Treat it as a reference implementation, not a framework: 17 stars, zero dependents, last release October 2021, single maintainer.

1. Install and see what you actually got

composer require vertigolabs/doctrine-full-text-postgres

Before writing any code, open src/ in vendor/vertigolabs/doctrine-full-text-postgres and read the two classes. Confirm the exact FQCNs — the README is the only documentation and its examples are the de-facto API. There is no bundle, no service provider, no autoconfiguration.

2. Register the type and the DQL function

Plain Doctrine:

use Doctrine\DBAL\Types\Type;
use Doctrine\ORM\Configuration;

Type::addType('tsvector', \VertigoLabs\DoctrineFullTextPostgres\DBAL\Types\TsVector::class);

$config = new Configuration();
$config->addCustomStringFunction('tsquery', \VertigoLabs\DoctrineFullTextPostgres\ORM\Query\AST\Functions\TsQueryFunction::class);

Symfony:

# config/packages/doctrine.yaml
doctrine:
    dbal:
        types:
            tsvector: VertigoLabs\DoctrineFullTextPostgres\DBAL\Types\TsVector
    orm:
        dql:
            string_functions:
                tsquery: VertigoLabs\DoctrineFullTextPostgres\ORM\Query\AST\Functions\TsQueryFunction

3. Add the column to an entity

/** @ORM\Column(type="tsvector", nullable=true) */
private ?string $searchVector = null;

4. Populate the vector — this is the step the package does not do for you

The vector must come from to_tsvector(...) evaluated inside Postgres, never from a PHP string you concatenated. The cheapest first cut is a lifecycle callback that issues a raw UPDATE, or better, a GENERATED ALWAYS AS (...) STORED column created by hand (see Implementation Patterns).

5. First query

$results = $em->createQuery(
    'SELECT a FROM App\Entity\Article a
     WHERE tsquery(a.searchVector, :q) = true'
)->setParameter('q', $userInput)->getResult();

Your first real use case: a single-table "search box" over one or two text columns (title, body) where you want stemming and relevance ordering without standing up Elasticsearch/Meilisearch. That is exactly the scope this package fits. Anything more — facets, multi-language, highlighting, fuzzy matching — pushes you into hand-written SQL anyway, at which point the package's value drops to two small classes.

Implementation Patterns

Prefer a generated column over a package-managed one. The 2021-era type only knows how to read/write a tsvector; it does not know how to compute it. Let Postgres do the work:

ALTER TABLE article ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,  '')), 'B')
  ) STORED;

CREATE INDEX article_search_idx ON article USING GIN (search_vector);

Note the explicit 'english' regconfig argument — this matters, see Gotchas. The entity column then becomes read-only: map it, never set it, and exclude it from forms/serializers.

If you are on PG < 12 (or the expression must be configurable), use a trigger instead and keep the entity mapping identical. Either way, one source of truth for the vector, no PHP-side string building.

Repository method with a typed parameter. Wrap the DQL in a repository so the query text lives in one place:

public function search(string $term, int $limit = 20, int $offset = 0): array
{
    return $this->createQueryBuilder('a')
        ->where('tsquery(a.searchVector, :q) = true')
        ->setParameter('q', $term)
        ->setMaxResults($limit)
        ->setFirstResult($offset)
        ->getQuery()
        ->getResult();
}

Choose the query parser deliberately. The DQL function almost certainly maps to to_tsquery, which throws on raw user input (foo &) and on bare punctuation. Normalise before binding, or sidestep it: build the tsquery in PHP or register additional DQL functions for plainto_tsquery (ANDs all words, never throws) and websearch_to_tsquery (PG 11+, understands quotes and -exclusion, ideal for a search box). These are three-line subclasses of the package's function — the cleanest legitimate use of its extension point.

Ranking requires your own DQL function. The package does not ship ts_rank. Register one:

$config->addCustomStringFunction('ts_rank', App\DQL\TsRankFunction::class);

$this->createQueryBuilder('a')
    ->select('a', 'ts_rank(a.searchVector, :q) AS HIDDEN rank')
    ->where('tsquery(a.searchVector, :q) = true')
    ->orderBy('rank', 'DESC')
    ->addOrderBy('a.id', 'DESC')   // mandatory tiebreaker
    ->setParameter('q', $term);

The HIDDEN alias is what makes ORDER BY rank legal in DQL without polluting the hydrated entity. The id tiebreaker is what keeps Doctrine\ORM\Tools\Pagination\Paginator from producing wrong page counts and duplicate rows across pages.

Weighting is a schema decision, not a query-time one. setweight(..., 'A') in the generated column plus ts_rank (or ts_rank_cd for cover-density) gives you "title beats body" for free. Retrofitting weights later means rewriting the column and reindexing.

Combine FTS with normal filters freely — it is just another AND in the same QueryBuilder. Do not try to compose it with LIKE fallbacks in the same expression; a tsquery that yields no lexemes (all stop words) returns zero rows, and you will want an explicit empty-query branch in the controller.

Snippets / highlighting (ts_headline) are not exposed by the package. Use native SQL via $em->getConnection()->executeQuery() with named parameters and map back to entities by ID, or register a third DQL function. Keeping it out of DQL is usually fine — snippets are a view concern.

Testing. SQLite and MySQL cannot even parse the schema; the tsvector type will emit invalid DDL there. Put all FTS tests behind a Postgres-only test group, or stub the repository in unit tests and reserve the real DB for one integration test that asserts the index is actually used (EXPLAIN contains Bitmap Index Scan on article_search_idx). That single assertion catches more regressions than any amount of result-count testing.

Gotchas and Tips

Maintenance risk is the headline. Last release 2021-10-04, one maintainer, no dependents. If you are on doctrine/orm:^3 or doctrine/dbal:^4, expect the DQL function's FunctionNode signatures and the type's getName() to be incompatible. Verify with a throwaway branch before committing to it. The classes are small enough (tens of lines) that vendoring or reimplementing them is a realistic and often better option — you get the same behaviour plus control over upgrades.

to_tsvector immutability decides whether your index exists. to_tsvector(text) uses default_text_search_config and is stable, so Postgres refuses it in an index expression or generated column. Always use the two-argument to_tsvector('english', text) form — that is immutable. This is the single most common reason people report "the package works but it's slow": no index was ever created.

A tsvector is not text. The type's PHP value is the Postgres text representation ('brown':3 'fox':4), with escaping and positions. Never setSearchVector('some words') — 'some words'::tsvector is a raw lexeme list with no stemming, and it will silently produce wrong matches. Never render it in an API response; it leaks document contents and can be megabytes. Mark it @Serializer\Ignore / #[Ignore].

Schema diff will fight you. doctrine:migrations:diff does not know about your GIN index or generated column, so it will happily generate DROP INDEX. Exclude them via the schema assets filter / schema_filter regex in doctrine.yaml, or hand-write the migration and keep the ORM mapping nullable and unmanaged.

= true in DQL is not decorative. Some ORM/DBAL combinations refuse a function call directly in a WHERE because they cannot infer a boolean return type. Comparing to true forces the expression through. If the DQL function has a getReturnType() you can also return BooleanType; otherwise keep the comparison.

Stop words and prefixes. to_tsquery('english', 'the') is an empty query and matches nothing. Words are stemmed, so running matches run but run does not match running in reverse, and no stemming dictionary will give you partial-word matching: cat will not find category. For typeahead-style behaviour you need to_tsquery('english', 'cat:*') — build it yourself and sanitise the input, because :* composition is injection-adjacent if you string-concatenate carelessly.

Language is a schema constant. A single 'english' config mis-stems names, product codes, and any non-English content. If you need multi-language, use one tsvector column per language (each independently indexed) rather than a single mixed column — mixing regconfigs into one vector is not supported meaningfully.

Write amplification is real. A GIN index on a wide body column is expensive on insert/update and the vectors themselves can dwarf the source text. Budget for it, and consider gin (search_vector) WITH (fastupdate = off) for read-heavy, batch-loaded tables, or GiST plus ts_rank_cd if you need @@ with distance operators.

Debug by looking at the SQL, not the DQL. $query->getSQL() shows you what the DQL function compiled to. Nine out of ten surprises ("it returns nothing", "it returns everything") are visible there — a mis-parenthesised @@, a bound parameter Doctrine cast to an array, or a vector column that is still NULL because the callback never ran on the rows you seeded.

Extension points, in order of usefulness: (1) subclass the DQL function to add plainto_tsquery/websearch_to_tsquery; (2) add ts_rank/ts_rank_cd/ts_headline functions; (3) subclass the DBAL type if you need convertToPHPValue to return null instead of the raw vector. Reach for (3) rarely — usually you want the column unmapped from the write path entirely and only touched by SQL.