Bambi Postgres Text Search Bundle Laravel Package

Getting Started

Minimal Steps

1. Installation

   composer require bambi/bambi-postgres-text-search-bundle
   

   Ensure your project uses Symfony 5.4+ and PostgreSQL 12+.

2. Enable the Bundle Add to config/bundles.php:

   return [
       // ...
       Bambi\PostgresTextSearchBundle\BambiPostgresTextSearchBundle::class => ['all' => true],
   ];
   

3. Configure API-Platform Filter Register the filter in config/packages/api_platform.yaml:

   api_platform:
       formats:
           jsonld:
               mime_types: ['application/ld+json']
       filters:
           text_search:
               type: Bambi\PostgresTextSearchBundle\Filter\TextSearchMatchFilter
               properties:
                   - { name: 'name', type: 'string' }
                   - { name: 'isbn', type: 'string' }
                   - { name: 'author.name', type: 'string' }
   

4. First Use Case Query your API with a ts_query parameter (or your custom name):

   GET /api/books?postgres_text_search=john%20&postgres_text_search=king
   

   This searches for books where name, isbn, or author.name contains "john" and "king".

Implementation Patterns

Workflows

1. Basic Text Search Use the default ts_query syntax for PostgreSQL full-text search:

   GET /api/books?ts_query=('book' & 'fiction') | 'author'
   

   • & = AND, | = OR, ! = NOT.
   • Parentheses group clauses.

2. Nested Entity Search Search across related entities (e.g., author.name):

   # config/services.yaml
   app.book.text_search_filter:
       class: Bambi\PostgresTextSearchBundle\Filter\TextSearchMatchFilter
       arguments:
           $properties:
               - name
               - isbn
               - author.name  # Searches Author#name
   

3. Custom Parameter Names Override the default ts_query parameter name:

   # config/services.yaml
   app.book.custom_search_filter:
       class: Bambi\PostgresTextSearchBundle\Filter\TextSearchMatchFilter
       arguments:
           $textSearchParameterName: 'custom_search'
   

   Query with:

   GET /api/books?custom_search=postgres
   

4. Integration with API-Platform Apply the filter to a specific operation in src/Entity/Book.php:

   use ApiPlatform\Metadata\Operation;
   use ApiPlatform\Metadata\GetCollection;
   
   #[GetCollection(
       filters: ['text_search'],
       uriTemplate: '/books{?postgres_text_search[]}'
   )]
   

5. Dynamic Property Mapping Use a service to dynamically define searchable properties:

   // src/Service/SearchPropertyProvider.php
   class SearchPropertyProvider
   {
       public function getSearchableProperties(): array
       {
           return [
               'name',
               'isbn',
               'author.name',
               'publisher.city' // Supports multi-level relations
           ];
       }
   }
   

   Inject into the filter:

   app.book.dynamic_filter:
       class: Bambi\PostgresTextSearchBundle\Filter\TextSearchMatchFilter
       arguments:
           $properties: '@=service("App\\Service\\SearchPropertyProvider").getSearchableProperties()'
   

Gotchas and Tips

Pitfalls

1. PostgreSQL Version Mismatch

   • The bundle is tested only with PostgreSQL 12. Upgrade or downgrade your PostgreSQL version if issues arise.
   • Verify compatibility with pg_config --version.

2. Case Sensitivity

   • PostgreSQL’s default text search is case-insensitive but diacritic-sensitive.
   • Use websearch_to_tsquery('english', 'café') for diacritic-insensitive searches.
   • Override the config string in the filter:

     arguments:
         $config: "'websearch'::regconfig"  # Diacritic-insensitive
     

3. Query Syntax Errors

   • Invalid ts_query syntax (e.g., unbalanced parentheses) will throw a 500 error.
   • Debug with raw SQL:

     // Dump the generated query in a filter
     $this->logger->debug('Generated SQL: ' . $query->getSQL());
     

4. Performance with Large Datasets

   • Full-text searches on unindexed columns are slow. Add a GIN index:

     CREATE INDEX idx_books_gin_search ON books USING GIN (
         to_tsvector('english', name || ' ' || isbn || ' ' || author->>'name')
     );
     

   • For complex queries, consider materialized views or search vectors.

5. Nested Relations Limitation

   • The bundle does not support arbitrary depth in relations (e.g., author.publisher.country).
   • Workaround: Denormalize data or use a custom DQL filter.

6. API-Platform Caching Issues

   • Filters may not work with cacheable operations due to dynamic query generation.
   • Disable caching for search endpoints or use cache: false in metadata.

Debugging Tips

1. Log Generated Queries Enable Doctrine logging in config/packages/dev/doctrine.yaml:

   doctrine:
       dbal:
           logging: true
           profiling: true
   

   Check logs for the raw SQL query.

2. Test with pgAdmin Manually test your ts_query in pgAdmin:

   SELECT * FROM books
   WHERE to_tsvector('english', name || ' ' || isbn) @@ to_tsquery('english', 'john & king');
   

3. Validate ts_query Syntax Use PostgreSQL’s plainto_tsquery for debugging:

   SELECT plainto_tsquery('english', 'john & king');
   

   • Returns NULL if syntax is invalid.

Extension Points

1. Custom Scoring Extend the filter to include ranking (e.g., ts_rank):

   // src/Filter/CustomTextSearchFilter.php
   class CustomTextSearchFilter extends TextSearchMatchFilter
   {
       protected function modifyQueryBeforeDql(string $dql, QueryBuilder $qb): string
       {
           $dql = parent::modifyQueryBeforeDql($dql, $qb);
           $dql = str_replace(
               'WHERE',
               'WHERE ts_rank(to_tsvector(''english'', ...), plainto_tsquery(''english'', :ts_query)) > 0',
               $dql
           );
           return $dql;
       }
   }
   

2. Add Highlighting Use PostgreSQL’s ts_headline for snippets:

   $qb->addSelect("ts_headline(to_tsvector('english', b.name), plainto_tsquery('english', :ts_query)) as highlight");
   

3. Support for Multiple Languages Override the config per filter:

   app.book.german_filter:
       arguments:
           $config: "'german'::regconfig"
   

4. Integration with Elasticsearch Use the bundle for hybrid search (PostgreSQL + Elasticsearch):

   • Fall back to Elasticsearch if the PostgreSQL query is too slow.
   • Example:

     if ($postgresResults->count() < 10) {
         $elasticsearchResults = $elasticsearchClient->search(...);
         return $postgresResults->merge($elasticsearchResults);
     }