Postgresql For Doctrine Laravel Package

Getting Started

Minimal Setup

1. Install the package:

   composer require martin-georgiev/postgresql-for-doctrine
   

2. Register types in AppServiceProvider (Laravel):

   use Doctrine\DBAL\Types\Type;
   use MartinGeorgiev\Doctrine\DBAL\Type as PostgresType;
   
   public function boot()
   {
       // Register core types
       Type::addType('jsonb', PostgresType\Jsonb::class);
       Type::addType('text[]', PostgresType\TextArray::class);
   
       // Register PostGIS types (if using PostGIS)
       Type::addType('geometry', PostgresType\Geometry::class);
       Type::addType('geography', PostgresType\Geography::class);
   
       // Register range types
       Type::addType('daterange', PostgresType\DateRange::class);
   }
   

3. First use case: JSONB column in Eloquent model:

   use Illuminate\Database\Eloquent\Model;
   use Doctrine\ORM\Mapping as ORM;
   
   class Product extends Model
   {
       #[ORM\Column(type: 'jsonb')]
       public array $metadata;
   
       // Usage
       $product->metadata = ['price' => 19.99, 'tags' => ['sale', 'premium']];
       $product->save();
   }
   

Where to Look First

• Available Types – Reference for all supported PostgreSQL types.
• Common Use Cases – Practical examples for arrays, JSONB, PostGIS, etc.
• Laravel Integration Guide – Laravel-specific setup (e.g., Doctrine integration).

Implementation Patterns

1. Type Registration Workflow

Pattern: Register types once in a service provider, then use them in models.

// app/Providers/DoctrineServiceProvider.php
public function register()
{
    $this->app->singleton(Doctrine\DBAL\Types\Type::class, function () {
        return Type::getTypesMap(); // Include custom types
    });
}

Tip: Use Type::overrideType() for existing Doctrine types (e.g., string → citext).

2. Eloquent + Doctrine Hybrid

Pattern: Combine Laravel Eloquent with Doctrine entities for complex queries.

// Hybrid model
class Post extends Model
{
    use \Doctrine\ORM\Mapping as ORM;

    #[ORM\Column(type: 'tsvector')]
    public string $searchVector;

    // Query using DQL (Doctrine Query Language)
    $posts = $this->createQueryBuilder()
        ->where('CONTAINS(:searchVector, :query) = TRUE')
        ->setParameter('query', $searchTerm)
        ->getQuery()
        ->getResult();
}

Tip: Use createQueryBuilder() for DQL and query() for raw SQL when needed.

3. PostGIS Spatial Queries

Pattern: Store geometries and query with PostGIS functions.

// Model
#[ORM\Column(type: 'geometry')]
public string $location;

// Query
$nearby = $this->createQueryBuilder('p')
    ->where('ST_DWithin(p.location, :point, 1000)') // Within 1km
    ->setParameter('point', 'POINT(-122.4194 37.7749)')
    ->getQuery()
    ->getResult();

Tip: Use WktSpatialData::fromWkt() to create geometries programmatically:

$point = WktSpatialData::fromWkt('POINT(-122.4194 37.7749)');

4. JSONB Operations

Pattern: Leverage JSONB’s native PostgreSQL functions.

// Insert
$product->metadata = ['specs' => ['weight' => 1.5, 'color' => 'red']];
$product->save();

// Query
$heavyProducts = $this->createQueryBuilder('p')
    ->where('JSON_GET_FIELD(p.metadata, \'$.specs.weight\') > 1')
    ->getQuery()
    ->getResult();

Tip: Use JsonbArray for typed JSON arrays:

#[ORM\Column(type: 'jsonb')]
public JsonbArray $tags;

5. Array Operations

Pattern: Use PostgreSQL array functions in DQL.

// Model
#[ORM\Column(type: 'text[]')]
public array $tags;

// Query
$searchTags = ['sale', 'premium'];
$query = $this->createQueryBuilder('p')
    ->where('ARRAY_CONTAINS(p.tags, :tag)')
    ->setParameter('tag', $searchTags[0])
    ->getQuery();

Tip: For complex array logic, use ANY() or ALL():

->where('ANY(p.tags = :tag)') // At least one match
->where('ALL(p.tags <@ :tags)') // Subset of tags

6. Range Types

Pattern: Use value objects for range types (e.g., DateRange, NumRange).

// Model
#[ORM\Column(type: 'daterange')]
public DateRange $eventDuration;

// Query
$upcomingEvents = $this->createQueryBuilder('e')
    ->where('e.eventDuration && :now') // Overlaps current date
    ->setParameter('now', new DateRange(new \DateTime(), new \DateTime('+1 year')))
    ->getQuery()
    ->getResult();

Tip: Use @> (contains) and <@ (contained by) for range checks:

->where('e.priceRange @> :minPrice') // Price range contains min

7. Custom Enums

Pattern: Define PostgreSQL enums in migrations and map to PHP.

// Migration
Schema::create('products', function (Blueprint $table) {
    $table->string('category')->useCurrent();
});

// Model
#[ORM\Column(type: 'string', enumType: 'category')]
public string $category;

Tip: Use EnumType for validation:

use MartinGeorgiev\Doctrine\DBAL\Types\EnumType;

class CategoryEnum extends EnumType
{
    public const SALE = 'sale';
    public const PREMIUM = 'premium';

    protected function getType(): string { return 'category'; }
}

8. Full-Text Search

Pattern: Use tsvector/tsquery for advanced search.

// Model
#[ORM\Column(type: 'tsvector')]
public string $searchIndex;

// Query
$searchTerm = 'premium sale';
$query = $this->createQueryBuilder('p')
    ->where('p.searchIndex @@ :query')
    ->setParameter('query', \Doctrine\DBAL\Types\TextSearchQuery::fromText($searchTerm))
    ->getQuery();

Tip: Update tsvector columns with to_tsvector():

$product->searchIndex = 'to_tsvector(\'english\', COALESCE(p.title, \'\') || \' \' || COALESCE(p.description, \'\'))';

9. Ltree Hierarchies

Pattern: Store hierarchical data (e.g., categories) as ltree.

// Model
#[ORM\Column(type: 'ltree')]
public string $categoryPath;

// Query
$subcategories = $this->createQueryBuilder('p')
    ->where('p.categoryPath <@ :path') // Descendants of path
    ->setParameter('path', 'Sports.Football')
    ->getQuery();

Tip: Use nlevel() or subpath() for path operations:

->where('nlevel(p.categoryPath) = 3') // Depth = 3

10. pgvector (Vector Search)

Pattern: Store embeddings and query with similarity.

// Model
#[ORM\Column(type: 'vector')]
public array $embedding; // [0.1, 0.5, ...]

// Query
$similarItems = $this->createQueryBuilder('i')
    ->where('i.embedding <=> :embedding < 0.5') // Cosine distance < 0.5
    ->setParameter('embedding', $queryEmbedding)
    ->getQuery();

Tip: Use L2 distance for Euclidean similarity:

->where('i.embedding <-> :embedding < 10') // L2 distance < 10

Gotchas and Tips

1. Type Conversion Pitfalls

• Issue: PostgreSQL text[] arrays may return strings (e.g., ["1", "2"]) instead of numbers. Fix: Explicitly cast in PHP:

  $numericTags =