Pgsql Doctrine Random Function Laravel Package

Getting Started

This package is a thin Doctrine ORM DQL extension: it registers PostgreSQL's RANDOM() (a double precision built-in) so it can be used inside DQL/QueryBuilder expressions. There is no service, no migration, no schema impact — it is a single AST FunctionNode parser. Expect ~10 minutes of work.

1. Install

composer require qbbr/pgsql-doctrine-random-function

2. Where to look first

Open the package source before doing anything else — the entire package is one small class implementing Doctrine\ORM\Query\AST\Functions\FunctionNode. Confirm the exact FQCN (typically Qbbr\PgsqlDoctrineRandomFunction\RandomFunction) and note that RANDOM() takes no arguments in its parse() method. The README will show the one line of registration config you actually need.

3. Register the function

Standalone Doctrine:

use Qbbr\PgsqlDoctrineRandomFunction\RandomFunction;

$config = new \Doctrine\ORM\Configuration();
// ... your existing setup
$config->addCustomNumericFunction('RANDOM', RandomFunction::class);

Symfony (config/packages/doctrine.yaml):

doctrine:
    orm:
        dql:
            numeric_functions:
                RANDOM: Qbbr\PgsqlDoctrineRandomFunction\RandomFunction

(Register it under numeric_functions rather than string_functions — RANDOM() returns a float, and this keeps Doctrine's type inference honest if you ever select it.)

4. First use case

The canonical reason you reached for this package: pull N random rows.

$users = $userRepository->createQueryBuilder('u')
    ->orderBy('RANDOM()')
    ->setMaxResults(10)
    ->getQuery()
    ->getResult();

Without the registration, DQL throws [Syntax Error] Error: Expected ... got 'RANDOM' — that parser error is the tell-tale sign you forgot step 3.

Implementation Patterns

Repository method wrapper. Hide the DQL in a repository so callers do not depend on the function name:

class UserRepository extends ServiceEntityRepository
{
    public function findRandom(int $limit = 1): array
    {
        return $this->createQueryBuilder('u')
            ->orderBy('RANDOM()')
            ->setMaxResults($limit)
            ->getQuery()
            ->getResult();
    }
}

Single random record. Combine with getOneOrNullResult() — do not use setMaxResults(1) plus getResult()[0], since an empty table would otherwise trigger an undefined-index notice:

public function findOneRandom(): ?User
{
    return $this->createQueryBuilder('u')
        ->orderBy('RANDOM()')
        ->setMaxResults(1)
        ->getQuery()
        ->getOneOrNullResult();
}

Scoped sampling. The ORDER BY RANDOM() runs after filtering, so it composes naturally with WHERE:

$this->createQueryBuilder('p')
    ->where('p.active = :active')
    ->andWhere('p.category = :cat')
    ->setParameter('active', true)
    ->setParameter('cat', $category)
    ->orderBy('RANDOM()')
    ->setMaxResults(5)
    ->getQuery()
    ->getResult();

Use it in a projection (e.g. attach a random sort key or a random weight) when you need the value itself rather than ordering:

->select('u.id AS id, RANDOM() AS sortKey')

Integration tip — keep it portable. If your codebase supports SQLite/MySQL in tests, wrap the random selection in the repository and provide a driver-aware fallback (RAND() in MySQL, RANDOM() in SQLite) or guard the DQL call behind if ($platform instanceof PostgreSQLPlatform). Never let RANDOM() leak into a query path that CI runs against non-Postgres databases.

Integration tip — DQL keyword casing. Doctrine's lexer upper-cases function names during parsing, but the registered key is matched against that upper-cased token. Register 'RANDOM' (upper case) and write RANDOM() in DQL to avoid mismatches.

Gotchas and Tips

• ORDER BY RANDOM() is a full-table sort. PostgreSQL cannot use an index for it; on a 1M-row table it is a seqscan plus sort every call. Fine for admin tools and small tables, dangerous in a hot endpoint. For large tables use TABLESAMPLE SYSTEM (n) (with post-filtering), or the "random offset between MIN(id) and MAX(id)" pattern, or cache a shuffled ID list. This package does not change that — it only makes the function callable from DQL.

• PostgreSQL only. Nothing here is portable. On MySQL the generated SQL RANDOM() is a syntax error at execution time — the DQL parse succeeds, the failure is at the DB layer, which makes the stack trace less obvious. Note this in your project docs.

• setMaxResults() is essential. Without it PostgreSQL returns the whole table in random order. There is no implicit limit.

• Do not use it for pagination. ORDER BY RANDOM() re-evaluates on every page request, so page 1 and page 2 return unrelated, possibly duplicate rows. Random sampling and pagination are mutually exclusive; sample once and pass the IDs around.

• RANDOM() takes no arguments. Passing anything (RANDOM(5)) will fail in parse() or produce invalid SQL, depending on the implementation. For seeded reproducibility you need setseed() + RANDOM(), which this function does not expose — drop to native SQL for that.

• Config caching in Symfony. After adding the numeric_functions entry, run bin/console cache:clear (or restart the dev server) — a stale compiled container will keep throwing the DQL syntax error even though your YAML looks right.

• Debugging. When a query misbehaves, dump the SQL with $query->getSQL() and $query->getParameters() before execution. If the function is not registered you will see the error at getSQL() time, not at getResult() time — useful for separating parser problems from driver problems.

• Extension points are limited by design. The package is one class; if you need setseed, weighted randomness, or a portable RAND(), subclass/decorate the AST node and register your own function under a different key rather than patching vendor code. There are no service hooks or config options to tune.

• Low maintenance signal (3 stars, 0 dependents). Pin the version in composer.json, and because the class is ~40 lines, consider vendoring it into your src/Doctrine/DQL/ if the project is long-lived — you get the same behavior with zero supply-chain surface and full control over the SQL it emits.