doctrine2 order by array with ids

Question

How I can with doctrine2 order by array with ids ?

I have this query:

$qb = $this->createQueryBuilder('u')
        ->select('u', 'n', 'c')
        ->leftJoin('u.notifications', 'n')
        ->leftJoin('u.channel', 'c')
        ->andWhere('u.id IN (:ids)')
        ->setParameter('ids', $ids);

I want that the result has the same order that array with ids, is possible do it ?

Thanks

SOLUTION:

Use FIELD mysql extension with https://github.com/beberlei/DoctrineExtensions

:)

Thanks

Bouke Versteegh · Answer 1 · 2018-04-02T13:01:14.777

6

Simple solution that doesn't require presorting query result:

 $idPositions = array_flip($userIds); // Mapping of id to position
 usort($users, function($userA, $userB) use ($idPositions) {
     return $idPositions[$userA->id] - $idPositions[$userB->id];
 });

edited Apr 02 '18 at 13:01

answered May 07 '15 at 22:56

Bouke Versteegh

4,097
1
39
35

score 0 · Answer 2 · edited May 23 '17 at 11:53

0

If you are using MySQL, sorting with the FIELD() function could accomplish this. DQL doesn't have built-in support for this function, so you'll either have to create a user defined function (see this answer), or create a native query.

edited May 23 '17 at 11:53

Community

1
1

answered May 23 '14 at 18:06

score 0 · Answer 3 · answered May 07 '15 at 22:45

I have a solution that is probably very inefficient, but it works. Make sure your results are ordered by id.

$users = $entityManager
    ->getRepository('User')
    ->findById($userIds, ['id' => 'ASC']);

$userIdsCopy = $userIds;
sort($userIdsCopy);
array_multisort($userIds, $userIdsCopy);
array_multisort($userIdsCopy, $users);

It's hard to explain how this works, but basically you "remember" the operations that happen when you sort the userId-array, then you apply the opposite of that to the query result.

score 0 · Accepted Answer · answered Feb 11 '16 at 17:05

0

I found the solution, it is possible using FIELD mysql extension with https://github.com/beberlei/DoctrineExtensions

Thanks

Marc

answered Feb 11 '16 at 17:05

Marc Morales Valldepérez

531
1
11
23

Here is an example of how to register this DQL function in Symfony and how to use it, just in case that you are using MySQL as well: https://ourcodeworld.com/articles/read/1162/how-to-order-a-doctrine-2-query-result-by-a-specific-order-of-an-array-using-mysql-in-symfony-5 – Carlos Delgado Mar 04 '20 at 21:54

score -1 · Answer 5 · answered May 25 '14 at 02:49

-1

Which id do you want to order by?

You can use...

->orderBy('u.id', 'asc')
// or n.id
// or c.id

Or you can use multiple order by's ("by's" doesn't seem right to me)...

->addOrderBy('u.id', 'asc')
->addOrderBy('u.name', 'desc') // if it exists

answered May 25 '14 at 02:49

qooplmao

17,622
2
44
69

doctrine2 order by array with ids

5 Answers5