{"record":{"id":"7e62abc49e82a60d","repo":"doctrine/orm","slug":"cannot-select-distinct-identifiers-from-query-with","errorCode":null,"errorMessage":"Cannot select distinct identifiers from query with LIMIT and ORDER BY on a column from a fetch joined to-many association. Use output walkers.","messagePattern":"Cannot select distinct identifiers from query with LIMIT and ORDER BY on a column from a fetch joined to-many association\\. Use output walkers\\.","errorType":"exception","errorClass":"RuntimeException","httpStatus":null,"severity":"error","filePath":"src/Tools/Pagination/LimitSubqueryWalker.php","lineNumber":131,"sourceCode":"            && $query->getMaxResults() !== null\n            && $AST->orderByClause\n            && count($fromRoot->joins)\n        ) {\n            // Check each orderby item.\n            // TODO: check complex orderby items too...\n            foreach ($AST->orderByClause->orderByItems as $orderByItem) {\n                $expression = $orderByItem->expression;\n                if (\n                    $orderByItem->expression instanceof PathExpression\n                    && isset($queryComponents[$expression->identificationVariable])\n                ) {\n                    $queryComponent = $queryComponents[$expression->identificationVariable];\n                    if (\n                        isset($queryComponent['parent'])\n                        && isset($queryComponent['relation'])\n                        && $queryComponent['relation']->isToMany()\n                    ) {\n                        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.');\n                    }\n                }\n            }\n        }\n    }\n\n    /**\n     * Retrieve either an IdentityFunction (IDENTITY(u.assoc)) or a state field (u.name).\n     *\n     * @return IdentityFunction|PathExpression\n     */\n    private function createSelectExpressionItem(PathExpression $pathExpression): Node\n    {\n        if ($pathExpression->type === PathExpression::TYPE_SINGLE_VALUED_ASSOCIATION) {\n            $identity = new IdentityFunction('identity');\n\n            $identity->pathExpression = clone $pathExpression;\n","sourceCodeStart":113,"sourceCodeEnd":149,"githubUrl":"https://github.com/doctrine/orm/blob/d9b9ff73016bf598ae07515f97289ce8074e97a5/src/Tools/Pagination/LimitSubqueryWalker.php#L113-L149","documentation":"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.","triggerScenarios":"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).","commonSituations":"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.","solutions":["Enable output walkers: $paginator->setUseOutputWalkers(true) (and remove any custom output walker hint on the query).","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.","Remove the to-many fetch join and load collections for the page separately, keeping the paginated query single-rooted."],"exampleFix":"// before\n$paginator = new Paginator($query); // $query joins u.orders and orders by o.createdAt\n$paginator->setUseOutputWalkers(false);\n\n// after\n$paginator = new Paginator($query);\n$paginator->setUseOutputWalkers(true);","handlingStrategy":"fallback","validationCode":null,"typeGuard":null,"tryCatchPattern":"// Try tree walkers (faster), fall back to output walkers for to-many ORDER BY queries\n$paginator = new Paginator(cloneQueryWithLimit($query));\n$paginator->setUseOutputWalkers(false);\ntry {\n    $rows = iterator_to_array($paginator);\n} catch (RuntimeException $e) {\n    if (! str_contains($e->getMessage(), 'fetch joined to-many association')) {\n        throw $e;\n    }\n    $paginator->setUseOutputWalkers(true); // correct but slower path\n    $rows = iterator_to_array($paginator);\n}","preventionTips":["Avoid ORDER BY on to-many joined columns in paginated queries","Denormalize sort keys (e.g. latest_order_at) onto the root entity","Run paginated queries through a fixture test that also asserts page contents are correctly ordered"],"tags":["doctrine-orm","paginator","pagination","fetch-join","to-many-association","order-by","output-walker"],"backgroundTag":"orm-paginator-output-walkers","analyzedSha":"d9b9ff73016bf598ae07515f97289ce8074e97a5","analyzedAt":"2026-08-21T06:13:15.863Z","schemaVersion":2},"datasetVersion":"2026-08-21T11:28:35.574Z"}