doctrine/orm · error · RuntimeException

Cannot select distinct identifiers from query with LIMIT and

Error message

Cannot select distinct identifiers from query with LIMIT and ORDER BY on a column from a fetch joined to-many association. Use output walkers.

What it means

LimitSubqueryWalker (the Paginator's tree-walker strategy) must produce a DISTINCT list of root ids to apply LIMIT. If the query combines a maxResults, at least one join, and an ORDER BY on a column of a fetch-joined to-many association, that distinct id list is inherently ambiguous (row multiplication from the join changes which row per id survives, so the ordering of the id set is not well defined). The walker detects this in validate() and refuses, pointing to output walkers which paginate by ROW_NUMBER instead.

Source

Thrown at src/Tools/Pagination/LimitSubqueryWalker.php:131

            && $query->getMaxResults() !== null
            && $AST->orderByClause
            && count($fromRoot->joins)
        ) {
            // Check each orderby item.
            // TODO: check complex orderby items too...
            foreach ($AST->orderByClause->orderByItems as $orderByItem) {
                $expression = $orderByItem->expression;
                if (
                    $orderByItem->expression instanceof PathExpression
                    && isset($queryComponents[$expression->identificationVariable])
                ) {
                    $queryComponent = $queryComponents[$expression->identificationVariable];
                    if (
                        isset($queryComponent['parent'])
                        && isset($queryComponent['relation'])
                        && $queryComponent['relation']->isToMany()
                    ) {
                        throw new RuntimeException('Cannot select distinct identifiers from query with LIMIT and ORDER BY on a column from a fetch joined to-many association. Use output walkers.');
                    }
                }
            }
        }
    }

    /**
     * Retrieve either an IdentityFunction (IDENTITY(u.assoc)) or a state field (u.name).
     *
     * @return IdentityFunction|PathExpression
     */
    private function createSelectExpressionItem(PathExpression $pathExpression): Node
    {
        if ($pathExpression->type === PathExpression::TYPE_SINGLE_VALUED_ASSOCIATION) {
            $identity = new IdentityFunction('identity');

            $identity->pathExpression = clone $pathExpression;

View on GitHub (pinned to d9b9ff7301)

Solutions

1. Enable output walkers: $paginator->setUseOutputWalkers(true) (and remove any custom output walker hint on the query).
2. Reorder by a root-entity column or by a precomputed aggregate (e.g. a denormalized 'latest_order_at' field) instead of a to-many joined column.
3. Remove the to-many fetch join and load collections for the page separately, keeping the paginated query single-rooted.

Example fix

// before
$paginator = new Paginator($query); // $query joins u.orders and orders by o.createdAt
$paginator->setUseOutputWalkers(false);

// after
$paginator = new Paginator($query);
$paginator->setUseOutputWalkers(true);

// Try tree walkers (faster), fall back to output walkers for to-many ORDER BY queries
$paginator = new Paginator(cloneQueryWithLimit($query));
$paginator->setUseOutputWalkers(false);
try {
    $rows = iterator_to_array($paginator);
} catch (RuntimeException $e) {
    if (! str_contains($e->getMessage(), 'fetch joined to-many association')) {
        throw $e;
    }
    $paginator->setUseOutputWalkers(true); // correct but slower path
    $rows = iterator_to_array($paginator);
}

When it happens

Trigger: Paginator iterating in tree-walker mode (setUseOutputWalkers(false), or query has HINT_CUSTOM_OUTPUT_WALKER set) a DQL where all of: $query->getMaxResults() !== null, the FROM root has joins, an orderByClause exists, and an ORDER BY item is a PathExpression whose identificationVariable is a joined to-many association component - e.g. 'SELECT u FROM User u JOIN u.orders o ORDER BY o.createdAt DESC' with ->setMaxResults(10).

Common situations: List pages sorting parents by their children (latest order, latest comment, max child date) while fetch-joining the children; developers disabling output walkers for pagination performance; sorting on joined table columns with to-many joins.

Related errors

• Paginating an entity with foreign key as identifier only wor
• Not all identifier properties can be found in the ResultSetM
• Can only process queries that select only one FROM component
• Cannot count query which selects two FROM components, cannot
• Setting a limit is not supported for delete or update querie

AI-assisted analysis of doctrine/orm@d9b9ff7301 (2026-08-21). Data as JSON: /api/errors/7e62abc49e82a60d. Report an issue: GitHub.