Doctrine2 custom records order

Question

I have simple query to get all pictures from database:

$this->createQueryBuilder('p')
        ->getQuery()
        ->getResult();

And I have array of ID's, which represents how they should be arranged in result array. How can I tell doctrine, to return these pictures in that specific order? For example, my "order array" is [3, 5, 9, 1, 10] and I want to get results in that particular order.

As pointed by @JasonRoman, you must accept the answer that solves your problem, in order to people with the same problem can find the solution easily. — chalasr, Mar 07 '16 at 20:34

score 2 · Accepted Answer · edited May 23 '17 at 12:07

There are several ways to do this. If you're using postgresql you can try one of the raw queries here: ORDER BY the IN value list . If you're using mysql you can try the suggestions here: Ordering by the order of values in a SQL IN() clause

If you instead want to do it through straight Doctrine you could create a generic mapper function that orders the results. For very large result sets you may hit performance issues since you have O(n^2). But it's a little simpler than trying to make your own custom Doctrine function to handle it, relying on database implementation.

Say your query returns 5 results with the following ids: (95, 4, 1, 33, 35). Now say you want them returned as (1, 35, 4, 33, 95):

$entities = $em->getRepository('YourBundle:Picture')->findBy(/*some criteria*/);

$orderFunction = function($id) use ($entities)
{
    foreach ($entities as $entity)
    {
        if ($entity->getId() == $id) {
            return $entity;
        }
    }
};

$orderAs = array(1, 35, 4, 33, 95);
$orderedEntities = array_map($orderFunction, $orderAs);

Now your $orderedEntities array will be in the order you specified.

You're welcome. If this fits your needs please accept my answer as the correct one so others can see that as well. — Jason Roman, Dec 22 '14 at 19:18

Doctrine2 custom records order

1 Answers1