Pgsql Doctrine Round Function Laravel Package

Getting Started

This package solves one narrow problem: Doctrine's DQL has no ROUND() function, so ROUND(AVG(o.total), 2) in DQL fails to parse. The package ships a custom DQL function node that translates to PostgreSQL's native ROUND() on the generated SQL. It is only useful if you are actually running Doctrine ORM inside Laravel (usually via laravel-doctrine/orm or mitchelvanw/laravel-doctrine), not stock Eloquent.

Minimal steps:

1. Confirm the Composer name before requiring it. The GitHub repo is kspitfire/pgsql-doctrine-round but the Packagist name implied here is kspitfire/pgsql-doctrine-round-function. Verify with composer show kspitfire/pgsql-doctrine-round-function or check Packagist directly, because the mismatch is a common trip-up for a package this small.

   composer require kspitfire/pgsql-doctrine-round-function
   

2. Find the actual class name — it is a single-file package, so just look:

   grep -r "^class " vendor/kspitfire/pgsql-doctrine-round-function/src
   

   It will be something like Kspitfire\...\RoundFunction, extending Doctrine\ORM\Query\AST\Functions\FunctionNode.

3. Register it against the EntityManager. The canonical Doctrine API is:

   // app/Providers/DoctrineServiceProvider.php
   use Doctrine\ORM\EntityManagerInterface;
   
   public function boot(EntityManagerInterface $em): void
   {
       $em->getConfiguration()->addCustomNumericFunction(
           'ROUND',
           \Kspitfire\PgsqlDoctrineRoundFunction\RoundFunction::class // confirm from step 2
       );
   }
   

   If your integration exposes Doctrine config, you may be able to do the same declaratively (e.g. a custom_numeric_functions / custom_dql_functions key in config/doctrine.php). Check the version of laravel-doctrine/orm you are on — the key name has changed between releases.

4. First use case — rounding an aggregate, which is what 90% of users want it for:

   $dql = 'SELECT ROUND(AVG(o.total), 2) AS avgTotal
           FROM App\Entities\Order o
           WHERE o.createdAt >= :since';
   
   $avg = $em->createQuery($dql)
       ->setParameter('since', $since)
       ->getSingleScalarResult();
   

Where to look first if it explodes: the generated SQL ($query->getSQL()), then the PostgreSQL error, then the package's parse()/getSql() methods — there are only two of them and they are short.

Implementation Patterns

Repository wrapper. Wrap the DQL in a repository method so callers don't think about function registration:

class OrderRepository
{
    public function averageTotalRounded(int $precision = 2): float
    {
        $dql = "SELECT ROUND(AVG(o.total), {$precision}) FROM App\Entities\Order o";

        return (float) $this->em->createQuery($dql)->getSingleScalarResult();
    }
}

Note the precision is interpolated, not bound — see Gotchas.

Inside a QueryBuilder. The function is available anywhere a DQL expression is parsed, including select, orderBy, and having:

$qb = $em->createQueryBuilder();
$qb->select('c.id', 'ROUND(SUM(i.amount), 2) AS total')
   ->from(Customer::class, 'c')
   ->join('c.invoices', 'i')
   ->groupBy('c.id')
   ->having('ROUND(SUM(i.amount), 2) > :threshold')
   ->setParameter('threshold', 1000);

$rows = $qb->getQuery()->getArrayResult();

Reporting/DTO hydration. Because the alias is generated SQL, getArrayResult() returns it as key total. If you map to a DTO with NEW, the alias must match the constructor parameter name — otherwise Doctrine silently passes null.

Percentages and ratios are the second-most-common use:

'ROUND(100.0 * SUM(CASE WHEN t.status = :done THEN 1 ELSE 0 END) / COUNT(t.id), 2) AS pct'

For this to work, t.id needs to be numeric or explicitly cast — see the double precision issue below.

Portability fallback. If you also run SQLite/MySQL in tests, don't register ROUND unconditionally against a single platform. Either register a per-platform class, or make the function itself platform-aware:

public function getSql(SqlWalker $sqlWalker): string
{
    $platform = $sqlWalker->getConnection()->getDatabasePlatform();
    $expr = $this->arithmeticExpression->dispatch($sqlWalker);
    $precision = $this->precision ? $this->precision->dispatch($sqlWalker) : null;

    if ($platform instanceof \Doctrine\DBAL\Platforms\PostgreSQLPlatform) {
        return sprintf('ROUND(CAST(%s AS numeric)%s)', $expr, $precision ? ", {$precision}" : '');
    }

    return $precision ? "ROUND({$expr}, {$precision})" : "ROUND({$expr})";
}

This is the single most valuable "extension point" — you get correct PostgreSQL semantics and passing CI.

Don't use it from Eloquent. In a normal Laravel app with Eloquent models, this package adds nothing. Use:

Order::selectRaw('ROUND(AVG(total)::numeric, 2) AS avg')->value('avg');

The ::numeric cast is required even here, for the same reason.

Gotchas and Tips

• ROUND(double precision, integer) does not exist in PostgreSQL. This is the #1 failure mode. If your column is float/double precision, ROUND(col, 2) throws function round(double precision, integer) does not exist. The package emits the SQL verbatim, so it cannot fix this for you. Fix: cast in DQL — ROUND(CAST(o.total AS numeric), 2) — or patch getSql() as shown above. ROUND(numeric, integer) and ROUND(double precision) are the only valid signatures.

• Precision arguments generally cannot be bound parameters. Doctrine's DQL parser resolves function arguments at compile time via an ArithmeticPrimary-style grammar; :precision is an InputParameter and will usually fail to parse. Inline the integer, and if it is user-controlled, validate it as an int before interpolation.

• Doctrine 3 / PHP 8 incompatibility is likely. The last release was September 2019, which puts it squarely in the Doctrine 2.x / Doctrine\ORM\Query\Lexer era. Doctrine ORM 3 removed Lexer, changed Parser internals, and altered the getSql() signature. Expect to fork the single class rather than composer update your way into it. With Doctrine 2.10+ you'll also see deprecation notices about the Lexer.

• The function is one class, 40-ish lines, no tests. With 0 stars and 0 dependents, treat this as a copy-paste snippet you vendor into your own App\Doctrine\DQL namespace, not a dependency. That also lets you fix the numeric cast and Doctrine 3 signatures in one pass.

• WHERE vs HAVING. ROUND() around an aggregate must live in HAVING (or a subquery); WHERE ROUND(AVG(...)) will produce a grouping error from PostgreSQL, same as raw SQL.

• Result types surprise. ROUND(numeric, 2) returns numeric, which PDO hands back as a string, not a float. (float) cast in your repository, or Doctrine will hydrate a string into a float-typed DTO property and TypeErrors will surface later than you'd like.

• Query cache staleness. Registering custom functions affects the parsed DQL cache key only indirectly; if you register the function after some queries have been compiled (e.g. in a lazily-booted provider), you can get "unknown function ROUND" from cached query plans. Clear DOCTRINE_QUERY_CACHE / the second-level cache after adding it.

• Debugging order: $q->getSQL() first (is ROUND(...) in the SQL at all?), then run that SQL directly in psql (is it the numeric cast issue?), then check the function registration (is addCustomNumericFunction vs addCustomStringFunction the right one? Numeric is correct here — it governs the function's return type classification, not its category).

• Package-name confusion. The GitHub slug and the Packagist slug differ. If composer require 404s, add an explicit VCS repository entry pointing at https://github.com/kspitfire/pgsql-doctrine-round and require it with the dev-master constraint, then pin the commit hash.