How to order a Doctrine 2 query result by a specific order of an array using MySQL in Symfony 5

Recently, working with a project that required the implementation of a fuzzy search engine based in PHP only, i noticed a special behaviour of a query that looked for multiple records based on the primary key (ID). Check the following code:

// The repository of some entity
$songsRepo = $this->getDoctrine()->getRepository(Songs::class);

// The id of the items that we want to find on the database
$ids = [5, 2, 3, 4, 1];

// Run the query using where IN to find by multiple ids
$results = $songsRepo->createQueryBuilder("song")
    ->where('song.id IN (:ids)')
    ->setParameter('ids', $ids)
    ->getQuery()
    ->getResult();

// We expect to obtain the rows with the specified order in the array
// However, the database will return the results in the following order:
// 1,2,3,4,5
// Thing that we obviously don't want if we gave a specific order 😡!
dump($results);

As you can see, although the given array has the specific order of searching for the following items on the database "5, 2, 3, 4, 1", the result will return them on the order that the database returns.

After reading the official documentation, questions etc. a thing was quickly clear to me, it seems that you cannot obtain your data with a custom order from your database using just the ORM. On non-mysql environments or PostgreSQL you may need to modify your logic and query every single row from the database to obtain the order that you want. Fortunately for me and maybe a lot of people, i use Doctrine 2 in symfony for the specific database vendor MySQL, which offers a way to solve this problem through the FIELD function.

In this article, you will learn how to create and register the custom FIELD function of MySQL for Doctrine 2 in Symfony 5.

1. Create Field Function

To get started, create the Field Function in the following directory of your application: /app/src/DQL/Mysql. The file will have the name FieldFunction.php and will contain the following code (this function is originally from the Beberlei/DoctrineExtensions):

<?php

// /app/src/DQL/Mysql/FieldFunction.php
namespace App\DQL\Mysql;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;

/**
 * @author Jeremy Hicks <[email protected]>
 */
class FieldFunction extends FunctionNode
{
    private $field = null;

    private $values = [];

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        // Do the field.
        $this->field = $parser->ArithmeticPrimary();

        // Add the strings to the values array. FIELD must
        // be used with at least 1 string not including the field.

        $lexer = $parser->getLexer();

        while (count($this->values) < 1 ||
            $lexer->lookahead['type'] != Lexer::T_CLOSE_PARENTHESIS) {
            $parser->match(Lexer::T_COMMA);
            $this->values[] = $parser->ArithmeticPrimary();
        }

        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        $query = 'FIELD(';

        $query .= $this->field->dispatch($sqlWalker);

        $query .= ', ';

        for ($i = 0; $i < count($this->values); $i++) {
            if ($i > 0) {
                $query .= ', ';
            }

            $query .= $this->values[$i]->dispatch($sqlWalker);
        }

        $query .= ')';

        return $query;
    }
}

By doing this, you will have the new following structure in your project:

SymfonyApp/
└── src/
    └── DQL/
        └── Mysql/
            └── FieldFunction.php

2. Register DQL Function

Now, you will need to register the previously created function on the doctrine.yaml configuration file like this:

# app/config/packages/doctrine.yaml
doctrine:
    # ...
    orm:
        # ...
        dql:
            string_functions:
                FIELD: App\DQL\Mysql\FieldFunction

This will register the function on your project, so you will be able to use the new function on your queries as we will describe in the following example.

3. Ordering results by an array of IDS

Finally, you just need to learn how to use the FIELD function on your queries. You only need to attach a new clause to your querybuilder, specifically the orderBy clause, specifying as value of the DQL part, the FIELD function that expects as first argument the name of the field that you want to sort the results by, in this case the id and as second parameter the array of ids:

$results = $this->getDoctrine()->getRepository(Entity::class)
    ->createQueryBuilder("a")
    ->where('a.id IN (:ids)')
    // Add the new orderBy clause specifying the order given in the ids array
    ->add("orderBy", "FIELD(a.id, :ids)")
    // Set the primary keys of the register that you want to search for
    ->setParameter('ids', [5, 2, 3, 4, 1])
    ->getQuery()
    ->getResult();

The following example shows how we could sort the results with the specific order of the array in our example of songs:

// The repository of some entity
$songsRepo = $this->getDoctrine()->getRepository(Songs::class);

// The id of the items that we want to find on the database
$ids = [5, 2, 3, 4, 1];

// Run the query using where IN to find by multiple ids
// Ordering the results by the specific order
$results = $songsRepo->createQueryBuilder("song")
    ->where('song.id IN (:ids)')
    // Add the new orderBy clause specifying the order
    ->add("orderBy", "FIELD(song.id, :ids)")
    ->setParameter('ids', $ids)
    ->getQuery()
    ->getResult();

// Now the rows will be ordered with the specified order in the array:
// 5, 2, 3, 4, 1
dump($results);

Happy coding ❤️!