Postgres Doctrine Extensions Laravel Package

Getting Started

Minimal Setup

1. Install the Package

   composer require avkluchko/postgres-doctrine-extensions
   

2. Register Extensions in config/packages/doctrine.yaml Add the custom DQL functions under doctrine.orm.dql.string_functions:

   doctrine:
       orm:
           dql:
               string_functions:
                   cast: AVKluchko\PostgresDoctrineExtensions\DQL\Cast
                   date_part: AVKluchko\PostgresDoctrineExtensions\DQL\DatePart
                   make_date: AVKluchko\PostgresDoctrineExtensions\DQL\MakeDate
                   to_char: AVKluchko\PostgresDoctrineExtensions\DQL\ToChar
   

3. First Use Case: Date Manipulation Use date_part and make_date in a repository method to filter records by date components:

   $qb = $this->createQueryBuilder('u');
   $qb->andWhere('make_date(:year, cast(date_part(\'month\', u.createdAt) as integer), cast(date_part(\'day\', u.createdAt) as integer)) = u.createdAt')
      ->setParameter('year', '2023');
   

Implementation Patterns

Query Builder Integration

• Dynamic Date Filtering Use date_part to extract components (e.g., date_part('year', u.createdAt)) and combine with make_date for flexible date ranges:

  $qb->andWhere('date_part(\'year\', u.createdAt) = :year')
     ->setParameter('year', $year);
  

• String Formatting with to_char Format dates or timestamps for display:

  $qb->select('to_char(u.createdAt, \'YYYY-MM-DD\') as formatted_date');
  

• Type Casting Ensure compatibility with Doctrine’s type system using cast:

  $qb->andWhere('cast(u.someField as integer) > 100');
  

Repository Methods

Encapsulate complex queries in repository methods:

public function findByMonth(int $year, int $month): array
{
    return $this->createQueryBuilder('u')
        ->andWhere('date_part(\'year\', u.createdAt) = :year')
        ->andWhere('date_part(\'month\', u.createdAt) = :month')
        ->setParameters(['year' => $year, 'month' => $month])
        ->getQuery()
        ->getResult();
}

Service Layer Usage

Pass formatted DQL strings to services for business logic:

public function getMonthlyReport(int $year, int $month): array
{
    $dql = 'SELECT to_char(u.createdAt, \'MMM YYYY\') as month, COUNT(u.id) as count
            FROM App\Entity\User u
            WHERE date_part(\'year\', u.createdAt) = :year
            AND date_part(\'month\', u.createdAt) = :month
            GROUP BY month';

    return $this->entityManager->createQuery($dql)
        ->setParameters(['year' => $year, 'month' => $month])
        ->getResult();
}

Gotchas and Tips

Pitfalls

1. Deprecated Package

   • Last updated in 2019; test thoroughly in production. Consider alternatives like gedmo/doctrine-extensions for active maintenance.

2. Doctrine Version Compatibility

   • Requires Doctrine ORM 2.5+. Ensure your composer.json aligns with the package’s constraints.

3. Case Sensitivity in DQL

   • PostgreSQL functions are case-insensitive, but Doctrine’s DQL parser may require exact matches (e.g., date_part vs DATE_PART).

4. Parameter Binding Quirks

   • Avoid binding values directly to date_part or make_date arguments. Use raw SQL for complex expressions:

     // ❌ Avoid (may cause SQL errors)
     $qb->andWhere('date_part(\'month\', u.createdAt) = :month')->setParameter('month', $month);
     
     // ✅ Use raw DQL for safety
     $qb->andWhere('EXTRACT(MONTH FROM u.createdAt) = :month');
     

Debugging

• Enable SQL Logging Add to .env:

  DOCTRINE_DQL_LOGGING=true
  

  Check logs for malformed DQL or SQL errors.

• Validate PostgreSQL Extensions Ensure your PostgreSQL server has the required extensions (e.g., pg_trgm for advanced string functions). Test with:

  SELECT * FROM pg_extension WHERE extname = 'postgis'; -- Example for PostGIS
  

Extension Points

1. Custom DQL Functions Extend the package by adding new functions in src/DQL/ and register them in doctrine.yaml:

   doctrine:
       orm:
           dql:
               string_functions:
                   custom_func: App\DQL\CustomFunction
   

2. Hybrid Queries Combine with native PostgreSQL functions for performance:

   $qb->andWhere('EXTRACT(YEAR FROM u.createdAt) = :year'); // Native PostgreSQL
   

3. Laravel-Specific Tips

   • Use DB::raw() in Eloquent queries for complex expressions:

     $users = User::whereRaw('date_part(\'year\', created_at) = ?', [$year])->get();
     

   • Cache compiled DQL queries in repositories to avoid redundant parsing.