Set LIMIT with doctrine 2?

Set LIMIT with doctrine 2?

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

LIMIT in Doctrine 2 Subqueries: Why setMaxResults Fails and How to Fix It

You are encountering a common pitfall in Doctrine ORM versioning where the API surface looks consistent, but the underlying SQL generation behaves differently depending on the context. In your specific scenario, you want to filter a main query based on IDs returned by a subquery that itself requires pagination (a LIMIT clause).

The core issue is that setMaxResults() in Doctrine 2 does not always translate directly to a LIMIT clause in the final SQL, particularly when used within a DQL subquery. The ORM often optimizes these queries for performance by materializing the result set of the subquery into an array or cache before executing the outer query. When you call setMaxResults(5) on the first query ($query_ids), Doctrine attempts to apply this limit, but depending on the database driver and the specific version of Doctrine, it might not generate the expected LIMIT clause in the SQL string if it decides to hydrate all results into memory first to ensure the grouping logic is correct.

Furthermore, your current approach involves manually constructing a DQL string ($query_select) using string concatenation to inject the subquery result. This is an anti-pattern that bypasses Doctrine's parameter binding and query optimization layers. When you pass $query_ids->getDQL() directly into a string, you lose the ability for Doctrine to manage the parameters safely or optimize the join between the two queries.

To achieve a true LIMIT behavior in this context, you need to reconsider how the data flows between your queries. If your goal is to get the top 5 distinct IDs that match certain criteria, and then fetch the full entities associated with those IDs ordered by creation date, you have a few architectural options:

Materialize the Subquery Results First

The most robust solution for complex filtering followed by ordering on the parent entity is to execute the subquery first, retrieve the IDs as a plain array, and then use that array in the IN clause of your second query. This decouples the pagination logic from the main query execution.

Instead of trying to force Doctrine to generate nested SQL with limits, you can do this:

// 1. Execute the subquery to get the top IDs
$subQuery = $this->getEntityManager()
    ->createQuery("SELECT e_.id FROM MuzichCoreBundle:Element e_ WHERE [...] GROUP BY e_.id")
    ->setMaxResults(5); // This usually works fine for simple ID selection

$topIds = array_map('intval', $subQuery->getResult());

// 2. Use the resulting IDs in the main query
$querySelect = "SELECT e FROM MuzichCoreBundle:Element e WHERE e.id IN (:ids) ORDER BY e.created DESC, e.name DESC";

$query = $this->getEntityManager()
    ->createQuery($querySelect)
    ->setParameters(['ids' => ':ids']); // Note: Doctrine 2 expects array parameters like ['ids' => $topIds]

Correction on parameter binding: In Doctrine 2, when using IN (:ids), you must pass the array directly to the setParameters method with a matching name. The correct usage is $query->setParameters(['ids' => $topIds]);. Ensure that the number of placeholders in your DQL matches the size of the array; if the subquery returns exactly 5 items, Doctrine will expand :ids into 5 distinct parameters automatically only if handled correctly by the DBAL layer. However, a safer pattern for dynamic arrays is often to use setParameter('ids', $topIds) where the parameter name matches the placeholder.

Why Your Original Code Didn't Work

In your original snippet, you attempted to inject the DQL string of the first query into the second. While this technically creates a nested query in some contexts, Doctrine's SQL generator for Doctrine 2 is sometimes conservative about adding LIMIT clauses inside subqueries unless explicitly told to treat them as raw SQL or if the database driver supports it natively in that structure.

More importantly, mixing DQL strings with manual concatenation prevents Doctrine from applying its internal caching and hydration strategies efficiently. By separating the queries into two distinct execution steps where the first returns a scalar list of IDs, you guarantee that the LIMIT is respected before the second query even attempts to run.

Trade-offs to Consider

If you require the full result set in a single row to avoid N+1 problems or transaction locking issues, you might consider using Doctrine's Query Builder instead of DQL strings for the main query. The builder provides better control over addSelect, whereIn, and ordering without falling back to string interpolation.

Alternatively, if you are working with a database that supports window functions (like PostgreSQL or MySQL 8.0+), you could write a single complex DQL query using SUBSTRING_INDEX or similar tricks, but this reduces portability across databases like SQLite or older MySQL versions. For a Laravel application specifically, leveraging the Query Builder alongside Doctrine often yields cleaner code for these "filter then sort" patterns without relying on fragile string manipulation.

Ultimately, the separation of concerns—filtering IDs first, then fetching entities second—is the standard pattern for handling LIMIT logic within subqueries in Doctrine 2 when the ORM's automatic SQL generation doesn't behave as expected with nested limits.

Our network