Doctrine Pgsql Bundle Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require blast-project/doctrine-pgsql-bundle
   

   Register the bundle in config/bundles.php:

   return [
       // ...
       Blast\DoctrinePgsqlBundle\BlastDoctrinePgsqlBundle::class => ['all' => true],
   ];
   

2. Configuration: Ensure your config/packages/doctrine.yaml includes PostgreSQL-specific settings:

   doctrine:
       dbal:
           url: '%env(DATABASE_URL)%'
           driver: 'pdo_pgsql'
           # ...
   

3. First Use Case: Replace LIKE queries with case-insensitive ILIKE behavior. Example:

   // Before: Case-sensitive LIKE
   $queryBuilder->andWhere('u.name LIKE :name')->setParameter('name', '%John%');
   
   // After: Automatically converted to ILIKE (case-insensitive)
   $queryBuilder->andWhere('u.name ILIKE :name')->setParameter('name', '%John%');
   

   The bundle handles this transparently for all LIKE queries.

Implementation Patterns

Query Builder Integration

• Automatic ILIKE Conversion: The bundle hooks into Doctrine’s query builder to replace LIKE with ILIKE for PostgreSQL. No manual changes are needed to existing queries.

  // Works out-of-the-box for all LIKE conditions
  $queryBuilder->andWhere('u.email LIKE :email')->setParameter('email', '%test@example.com');
  

• Regex Substring Support: Use PostgreSQL’s substring(field FROM regexp) syntax via DQL:

  $queryBuilder->select('substring(u.username FROM \'^[A-Za-z]+\') as usernamePrefix');
  

Entity and Repository Patterns

• Custom DQL Functions: Extend the bundle’s functionality by adding custom PostgreSQL functions. Example:

  // In a custom DQL function (e.g., for `regexp_matches`):
  $this->getEntityManager()->getConnection()->getDatabasePlatform()
      ->registerDoctrineTypeMapping('regexp_matches', 'string');
  

• Sonata Admin Integration: If using SonataAdminBundle, override the default LIKE filters to leverage ILIKE:

  // In a custom filter
  $queryBuilder->andWhere('e.name ILIKE :name')->setParameter('name', '%' . $value . '%');
  

Performance Considerations

• Indexing: Ensure PostgreSQL indexes are optimized for ILIKE/regexp queries. Example:

  CREATE INDEX idx_user_name_gin ON users USING gin (to_tsvector('english', name));
  

• Batch Processing: For large datasets, use fetchAllAssociative() with ILIKE queries to avoid memory issues:

  $results = $entityManager->createQuery('SELECT u FROM App\Entity\User u WHERE u.name ILIKE :name')
      ->setParameter('name', '%search%')
      ->setMaxResults(1000)
      ->getResult();
  

Gotchas and Tips

Pitfalls

• Case Sensitivity in LIKE: The bundle only converts LIKE to ILIKE. Ensure all manual queries use ILIKE for case-insensitive searches:

  // ❌ Won't be converted (manual query)
  $query = "SELECT * FROM users WHERE name LIKE '%test%'";
  
  // ✅ Automatically converted
  $queryBuilder->where('u.name LIKE :name');
  

• PostgreSQL-Specific Functions: Not all PostgreSQL functions are supported by default. For unsupported functions (e.g., regexp_replace), use raw SQL:

  $queryBuilder->andWhere('regexp_replace(u.email, \'@example\.com\', \'\', \'g\') = :cleanedEmail');
  

• Doctrine Cache Invalidation: After adding custom DQL functions, clear the Doctrine cache:

  php bin/console doctrine:cache:clear-metadata
  php bin/console doctrine:cache:clear-query
  

Debugging

• Query Logging: Enable SQL logging to verify ILIKE conversion:

  # config/packages/dev/doctrine.yaml
  doctrine:
      dbal:
          logging: true
          logging_format: '%%timestamp_extra%% %%connection%% %%statement%% %%params%% %%type%%'
  

• Platform-Specific Checks: Confirm the bundle is active by checking the database platform:

  $platform = $entityManager->getConnection()->getDatabasePlatform();
  if ($platform instanceof \Doctrine\DBAL\Platforms\PostgreSQLPlatform) {
      // Bundle features are available
  }
  

Extension Points

• Custom Query AST Nodes: Extend the bundle by adding new AST nodes for PostgreSQL-specific functions. Example:

  // In a custom Doctrine extension
  $platform->registerDoctrineTypeMapping('jsonb', 'array');
  

• Event Subscribers: Listen to query.sql events to modify SQL before execution:

  // src/EventListener/PgsqlQueryListener.php
  public function onQuerySql(QuerySqlEventArgs $event) {
      $sql = $event->getSql();
      if (strpos($sql, 'LIKE') !== false) {
          $sql = str_replace('LIKE', 'ILIKE', $sql);
          $event->setSql($sql);
      }
  }
  

• Configuration Overrides: Disable ILIKE conversion globally in config/packages/blast_doctrine_pgsql.yaml:

  blast_doctrine_pgsql:
      ilike_conversion: false