Symfony中如何用OrderBy注解按字符串列表排序多对多关联实体

Hey there! Let's figure out how to get that custom string sequence sorting working for your ManyToMany entities. The core issue here is that Doctrine's built-in @OrderBy annotation doesn't natively support SQL functions like FIELD()—which is exactly what you need to sort by a specific list of string values. Let's walk through the best solutions:

1. Manual Sorting in Repository Queries (Recommended) #

This is the most flexible approach, especially if you might need to adjust the sort sequence dynamically. You'll build a custom DQL query that uses the FIELD() function (or its equivalent for your database) to define the order.

Example for MySQL/MariaDB #

Suppose you have a Team entity with a ManyToMany association to Player, and you want to sort players by their role in the sequence ['captain', 'midfielder', 'defender']:

// src/Repository/TeamRepository.php
namespace App\Repository;

use App\Entity\Team;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;

class TeamRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Team::class);
    }

    public function findTeamsWithPlayersOrderedByRole()
    {
        $roleOrder = ['captain', 'midfielder', 'defender'];

        return $this->createQueryBuilder('t')
            ->leftJoin('t.players', 'p')
            ->addSelect('p') // Ensure players are fetched with teams
            ->orderBy('FIELD(p.role, :roleOrder)', 'ASC')
            ->setParameter('roleOrder', $roleOrder)
            ->getQuery()
            ->getResult();
    }
}

Example for PostgreSQL #

PostgreSQL uses ARRAY_POSITION instead of FIELD(), so adjust the order clause like this:

->orderBy('ARRAY_POSITION(ARRAY[:roleOrder]::text[], p.role)', 'ASC')

2. Custom Doctrine DQL Function (For Annotation-Based Sorting) #

If you really want to use the @OrderBy annotation directly in your entity, you'll need to register a custom DQL function that supports FIELD(). Here's how:

Step 1: Create the Custom Function Class #

// src/Doctrine/Query/Function/FieldFunction.php
namespace App\Doctrine\Query\Function;

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

class FieldFunction extends FunctionNode
{
    private $field = null;
    private $values = [];

    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        
        $this->field = $parser->ArithmeticPrimary();
        
        while ($parser->getLexer()->isNextToken(Lexer::T_COMMA)) {
            $parser->match(Lexer::T_COMMA);
            $this->values[] = $parser->ArithmeticPrimary();
        }
        
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(SqlWalker $sqlWalker)
    {
        $parts = [
            $this->field->dispatch($sqlWalker),
            ...array_map(fn($val) => $val->dispatch($sqlWalker), $this->values)
        ];
        
        return 'FIELD(' . implode(', ', $parts) . ')';
    }
}

Step 2: Register the Function in Doctrine Configuration #

If you're using Symfony, add this to config/packages/doctrine.yaml:

doctrine:
    orm:
        dql:
            string_functions:
                FIELD: App\Doctrine\Query\Function\FieldFunction

Step 3: Use It in the Entity Annotation #

Now you can define the sort order directly in your Team entity:

// src/Entity/Team.php
use Doctrine\ORM\Mapping as ORM;
use App\Entity\Player;

/**
 * @ORM\Entity(repositoryClass=TeamRepository::class)
 */
class Team
{
    // ... other fields

    /**
     * @ORM\ManyToMany(targetEntity=Player::class)
     * @ORM\JoinTable(name="team_players")
     * @ORM\OrderBy({"role": "FIELD('captain', 'midfielder', 'defender')"})
     */
    private $players;

    // ... getters/setters
}

Note: This approach locks in the sort sequence—if you need to change it dynamically, stick with the repository query method instead.

Quick Note on Propel ORM #

You mentioned Propel supports Order By FIELD directly, which is true—Propel has native support for this in its model configuration. But since you're working with Doctrine, the above methods will get you the same result.

内容的提问来源于stack exchange，提问作者episch