Postgre Search Bundle Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require ddmaster/postgre-search-bundle:dev-master
   

   Add to AppKernel.php:

   new Ddmaster\PostgreSearchBundle\PostgreSearchBundle(),
   

2. Configure DBAL and ORM in config.yml:

   doctrine:
       dbal:
           types:
               tsvector: Ddmaster\PostgreSearchBundle\Dbal\TsvectorType
           mapping_types:
               tsvector: tsvector
       orm:
           entity_managers:
               default:
                   dql:
                       string_functions:
                           TSQUERY: Ddmaster\PostgreSearchBundle\DQL\TsqueryFunction
                           TSRANK: Ddmaster\PostgreSearchBundle\DQL\TsrankFunction
   

3. First Use Case: Add a tsvector field to an entity (e.g., Article):

   use Doctrine\ORM\Mapping as ORM;
   
   

/**

• @ORM\Entity / class Article { /*
  • @ORM\Column(type="tsvector") */ private $searchContent; // ... }

  Query with full-text search:
  ```php
  $query = $entityManager->createQuery(
      'SELECT a FROM AppBundle:Article a
       WHERE a.searchContent @@ :query'
  )->setParameter('query', $entityManager->getConnection()->getDatabasePlatform()->getTsQueryParameter('search term'));
  

Implementation Patterns

Core Workflows

1. Indexing Data:

   • Use tsvector columns to store pre-computed search vectors (e.g., via lifecycle callbacks or cron jobs).
   • Example: Update searchContent in prePersist/preUpdate:

     public function prePersist(LifecycleEventArgs $args) {
         $entity = $args->getEntity();
         $entity->setSearchContent($this->generateTsvector($entity));
     }
     

2. Querying:

   • Basic Search: Use @@ operator for exact matches:

     $query = $em->createQuery('SELECT a FROM AppBundle:Article a WHERE a.searchContent @@ :query')
         ->setParameter('query', $platform->getTsQueryParameter('laravel symfony'));
     

   • Ranked Results: Combine with ts_rank for relevance scoring:

     $query = $em->createQuery('
         SELECT a, TS_RANK(a.searchContent, :query) as rank
         FROM AppBundle:Article a
         WHERE a.searchContent @@ :query
         ORDER BY rank DESC
     ');
     

3. Dynamic Indexing:

   • Rebuild tsvector columns on-the-fly (e.g., for partial updates):

     $entity->setSearchContent($em->getConnection()->getDatabasePlatform()->getTsvectorExpression(
         ['title', 'content'], ['title', 'content']
     ));
     

Integration Tips

• Symfony Forms: Add a search form with a text field for queries:

  $builder->add('query', TextType::class, [
      'attr' => ['placeholder' => 'Search...']
  ]);
  

• APIs: Return ranked results as JSON:

  $results = $query->getResult();
  return $this->json(array_map(fn($item) => [
      'id' => $item[0]->getId(),
      'title' => $item[0]->getTitle(),
      'rank' => $item[1]
  ], $results));
  

• Caching: Cache frequent queries (e.g., with StashBundle) to avoid recomputing tsvector values.

Gotchas and Tips

Pitfalls

1. Outdated Bundle:

   • Last release in 2019 may lack compatibility with newer Doctrine/PostgreSQL versions.
   • Workaround: Fork and update dependencies (e.g., doctrine/dbal, doctrine/orm) manually.

2. Case Sensitivity:

   • PostgreSQL tsvector is case-sensitive by default. Use to_tsvector('english', ...) for case-insensitive searches or configure a custom dictionary:

     $platform->getTsvectorExpression(['title'], ['title'], 'english');
     

3. Performance:

   • Avoid recomputing tsvector in queries: Store pre-computed values in the DB.
   • Index tsvector columns:

     CREATE INDEX idx_article_search_content ON article USING gin(search_content);
     

4. DQL Function Conflicts:

   • If TSQUERY/TSRANK clash with existing functions, alias them:

     string_functions:
         TSQUERY: Ddmaster\PostgreSearchBundle\DQL\TsqueryFunction
         TSRANK_CUSTOM: Ddmaster\PostgreSearchBundle\DQL\TsrankFunction
     

     Then use TSRANK_CUSTOM in queries.

Debugging

• Check PostgreSQL Logs: Enable log_min_messages = LOG in postgresql.conf to debug tsvector operations.
• Validate Queries: Use EXPLAIN ANALYZE in PostgreSQL to verify query plans:

  EXPLAIN ANALYZE SELECT * FROM article WHERE search_content @@ to_tsquery('search term');
  

• Test with Raw SQL: Bypass DQL to isolate issues:

  $conn->executeQuery('SELECT * FROM article WHERE search_content @@ :query', ['query' => 'search term']);
  

Extension Points

1. Custom Tokenization:

   • Override TsvectorType to support custom tokenizers (e.g., for URLs or special formats):

     class CustomTsvectorType extends TsvectorType {
         public function convertToDatabaseValueSql($value, AbstractPlatform $platform) {
             return $platform->getTsvectorExpression(
                 $value, ['title', 'content'], 'custom_dictionary'
             );
         }
     }
     

     Register in config.yml:

     doctrine:
         dbal:
             types:
                 tsvector: AppBundle\DBAL\CustomTsvectorType
     

2. Add More DQL Functions:

   • Extend the bundle by adding new functions (e.g., plainto_tsquery):

     // src/DQL/PlainToTsqueryFunction.php
     class PlainToTsqueryFunction extends AbstractFunctionNode {
         public function parse(\Doctrine\ORM\Query\Parser $parser) { /* ... */ }
     }
     

     Register in config.yml:

     doctrine:
         orm:
             entity_managers:
                 default:
                     dql:
                         string_functions:
                             PLAIN_TO_TSQUERY: AppBundle\DQL\PlainToTsqueryFunction
     

3. Event Listeners:

   • Hook into prePersist/preUpdate to auto-generate tsvector:

     $eventManager->addEventListener(
         KernelEvents::CONTROLLER,
         function (ControllerEvent $event) {
             $entity = $event->getController()->getArgument('article');
             if ($entity instanceof Article) {
                 $entity->setSearchContent($this->generateTsvector($entity));
             }
         }
     );