Doctrine Extensions Laravel Package

Product Decisions This Supports

• Database-Driven Feature Development:

  • Enable complex date/time calculations (e.g., time zone conversions, date diffs) without custom SQL or repository methods.
  • Support financial calculations (e.g., MoneyType, PercentType) for e-commerce, billing, or analytics platforms.
  • Simplify aggregation queries (e.g., GROUP_CONCAT for MySQL) to reduce backend complexity.

• Roadmap Prioritization:

  • Build vs. Buy: Avoid reinventing DQL functions for common use cases (e.g., TIMESTAMPDIFF, DATE_FORMAT), reducing dev time.
  • Multi-DB Compatibility: Standardize queries across MySQL/PostgreSQL for global applications (e.g., SaaS platforms with regional deployments).
  • Performance Optimization: Replace inefficient native queries (e.g., WHERE YEAR(date_column) = 2023) with DQL functions for better query planning.

• Use Cases:

  • Analytics Dashboards: Calculate time-based metrics (e.g., "users active in the last 7 days") via TIMESTAMPDIFF.
  • Localization: Handle time zones (e.g., CONVERT_TZ) for user-facing timestamps in multi-regional apps.
  • Data Migration: Transform legacy data formats (e.g., DATE_FORMAT for ISO 8601 parsing).
  • Financial Systems: Validate/format monetary values using MoneyType/PercentType for compliance.

When to Consider This Package

• Adopt When:

  • Your Laravel/PHP app relies on Doctrine ORM (not Eloquent) for complex queries.
  • You need database-specific functions (e.g., MySQL’s GROUP_CONCAT, PostgreSQL’s DATE_TRUNC) but want DQL compatibility.
  • Your team prioritizes maintainability over raw performance (e.g., avoiding raw SQL in repositories).
  • You’re using PHP 8.1+ and Doctrine ORM 3.0+ (or maintain a 2.x branch separately).
  • Your use case involves date/time math, string aggregation, or type casting in queries.

• Look Elsewhere If:

  • You’re using Eloquent (this package is Doctrine-specific).
  • Your queries are simple CRUD (no need for DQL extensions).
  • You require SQL Server or SQLite support (only MySQL/PostgreSQL are officially supported).
  • Your team lacks Doctrine ORM expertise (setup requires configuration).
  • You need real-time performance (DQL functions may add slight overhead vs. native SQL).

How to Pitch It (Stakeholders)

For Executives:

"This package lets us leverage advanced database functions (like time zone conversions or financial calculations) directly in our queries—without writing custom SQL or bloating our codebase. For example, we can simplify complex analytics queries (e.g., 'users active in the last 30 days') using built-in DQL functions, reducing backend complexity and speeding up development. It’s a low-risk, high-reward way to standardize database operations across our MySQL/PostgreSQL environments, especially for global features like localization or billing."

For Engineering:

*"Oro Doctrine Extensions provides pre-built DQL functions for common tasks (e.g., TIMESTAMPDIFF, GROUP_CONCAT, MoneyType) that would otherwise require custom repositories or raw SQL. Key benefits:

• Faster Development: No need to reinvent date/time/string functions.
• Multi-DB Support: Works seamlessly with MySQL/PostgreSQL (avoids platform-specific queries).
• Doctrine 3.0 Ready: Modernized for PHP 8.1+ and latest Doctrine.
• Extensible: Can add custom functions or platforms if needed.

Trade-offs:

• Requires Doctrine ORM (not Eloquent).
• Minor setup (registering functions in config).
• Slight abstraction overhead vs. native SQL.

Recommendation: Use for complex queries (analytics, financial, localization) where DQL functions add clarity. For simple CRUD, stick with Eloquent or native SQL."*

Example Use Case: *"Instead of writing this raw SQL for a report:

SELECT
  user_id,
  GROUP_CONCAT(DISTINCT tag.name) as tags
FROM user_tags
JOIN tags ON tag.id = user_tags.tag_id
WHERE user_tags.user_id = 123
GROUP BY user_id

We can use GROUP_CONCAT in DQL:

$qb->select('u.id', 'GROUP_CONCAT(DISTINCT t.name) as tags')
  ->from('App\Entity\User', 'u')
  ->join('u.tags', 't')
  ->where('u.id = :id')
  ->setParameter('id', 123);

Cleaner, more maintainable, and database-agnostic."*