如何在Symfony 5中使用MySQL通过数组的特定顺序对Doctrine 2查询结果进行排序

本文概述

• 1.创建字段功能
• 2.注册DQL功能
• 3.按IDS数组排序结果

最近, 在与一个仅需要基于PHP实现模糊搜索引擎的项目一起工作时, 我注意到了一种查询的特殊行为, 该查询基于主键(ID)查找多个记录。检查以下代码：

// 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);

如你所见, 尽管给定数组具有在数据库” 5、2、3、4、1″上搜索以下项目的特定顺序, 但是结果将按照数据库返回的顺序返回它们。

在阅读了官方文档, 问题等之后, 我很快就发现一件事情, 看来你无法仅使用ORM从数据库中以自定义顺序获取数据。在非MySQL环境或PostgreSQL上, 你可能需要修改逻辑并从数据库中查询每一行以获得所需的顺序。对于我甚至很多人来说, 幸运的是, 我在Symfony中将Doctrine 2用于特定的数据库供应商MySQL, 它提供了一种通过FIELD函数解决此问题的方法。

在本文中, 你将学习如何在Symfony 5中为Doctrine 2创建和注册MySQL的自定义FIELD函数。

1.创建字段功能

首先, 在应用程序的以下目录中创建字段函数：/ app / src / DQL / Mysql。该文件的名称为FieldFunction.php, 并将包含以下代码(此函数最初来自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 <jeremy.hicks@gmail.com>
 */
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;
    }
}

这样, 你的项目中将具有以下新结构：

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

2.注册DQL功能

现在, 你将需要在doctrine.yaml配置文件中注册以前创建的函数, 如下所示：

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

这将在你的项目中注册该功能, 因此你将能够在查询中使用新功能, 如以下示例中所述。

3.按IDS数组排序结果

最后, 你只需要学习如何在查询中使用FIELD函数即可。你只需要在你的querybuilder上附加一个新子句, 特别是orderBy子句, 将FIELD函数指定为DQL部分的值, 该函数期望将第一个参数作为你要对结果进行排序的字段的名称, 在这种情况下id和id数组作为第二个参数：

$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 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);

编码愉快❤️！