Symfony 2 Doctrine find by ordered array of id

Symfony 2 Doctrine find by ordered array of id

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

Stefan Bogdanescu

Founder & Senior Architect

Preserving Order When Fetching Entities by ID in Doctrine 2

When working with Symfony 2 and Doctrine ORM, a common requirement is to retrieve a collection of entities based on a specific list of primary keys. However, standard queries often fail to respect the order of that input array. Consider a scenario where you have a ListCards entity storing an array of IDs like ["16", "2", "84"]. Your goal isn't just to fetch these three cards; you need them returned in the exact sequence defined by the list: Card 16, then Card 2, then Card 84.

Using $cardRepository->findBy(["id" => ["16", "2", "84"]]) is the most immediate approach, but it will almost certainly return results sorted by id in ascending order (i.e., 2, 16, 84). Doctrine's standard findBy method translates the second argument into a simple IN clause (WHERE id IN (:ids)), which does not inherently preserve array ordering. Furthermore, trying to pass an ORDER BY clause directly within the findBy arguments is not supported by Doctrine's API.

To solve this in Symfony 2 with Doctrine 2, you must construct a custom DQL query or use the repository's createQueryBuilder method. The core logic requires two distinct parts: filtering by the specific IDs and explicitly ordering the results by those same IDs.

Here is how you implement this using the Query Builder:

$idsArray = ["16", "2", "84"];

$qb = $cardRepository->createQueryBuilder('c')
    ->where('c.id IN (:ids)')
    ->setParameter('ids', $idsArray)
    ->orderBy('FIELD(c.id, :ids)', 'ASC'); // Note: FIELD is MySQL specific. For PostgreSQL/SQL Server, logic differs.

The ORDER BY clause in DQL generally only supports simple columns, expressions, or literals. It does not natively support the SQL FIELD() function used for array-based sorting on all database platforms. This creates a platform-specific bottleneck.

Platform-Specific Solutions

If your application runs on MySQL, you can leverage the FIELD function within the DQL query:

$qb = $cardRepository->createQueryBuilder('c')
    ->where('c.id IN (:ids)')
    ->setParameter('ids', $idsArray)
    ->addOrderBy('FIELD(c.id, :firstId, :secondId, :thirdId)', 'ASC');

While this works for small arrays, hardcoding parameters like :firstId and :secondId is not scalable. A more robust approach in MySQL involves using a subquery or joining a derived table to map the IDs to their original positions. However, Doctrine's DQL layer sometimes restricts complex joins involving derived tables depending on the version.

A cleaner, database-agnostic solution often involves fetching the entities first and then sorting them in PHP after retrieval, provided the dataset is not massive. Since you are filtering by a specific list of IDs, the result set will be small (e.g., 3 or 4 rows). Sorting an array of three items in memory using usort or array_multisort is computationally negligible compared to the overhead of complex SQL joins.

$cards = $cardRepository->findBy([], ['id' => 'ASC']); // Fetch filtered set if needed, usually find by ID returns sorted
// Actually, finding by ID usually returns random or ASC order depending on DB.
// Better approach:
$qb = $cardRepository->createQueryBuilder('c')
    ->where('c.id IN (:ids)')
    ->setParameter('ids', $idsArray);
$cards = $qb->getQuery()->getResult();

// Sort in PHP based on the original order
$orderMap = array_flip($idsArray); // ["84" => 2, "16" => 0, "2" => 1]
usort($cards, function ($a, $b) use ($orderMap) {
    return $orderMap[$a->getId()] - $orderMap[$b->getId()];
});

This pattern ensures database independence. You avoid relying on FIELD() which breaks on PostgreSQL or SQL Server, and you bypass the limitations of Doctrine's DQL ORDER BY. The trade-off is a minimal amount of CPU time spent on sorting in PHP, which is acceptable for small result sets.

When to Use Custom Repositories

For recurring patterns like this, encapsulating the logic within a custom repository method is advisable. This keeps the controller or service layer clean and documents the specific requirement (ordered retrieval) directly in the codebase.

If you are migrating from Symfony 2 to modern stacks where Doctrine ORM supports more complex subqueries or if you find yourself needing this functionality frequently across different database drivers, evaluating your stack for native support of array-based sorting might be worthwhile. For instance, some Laravel extensions or specific packages offer helper methods that abstract these low-level DQL constraints.

Ultimately, the most reliable way to guarantee order when Doctrine cannot natively express "order by this arbitrary list" is to fetch the matching records and apply a lightweight sort in PHP using an index map derived from your original array. This approach respects the data model while adhering to the limitations of the underlying SQL dialects.

Our network