Symfony6/7中Doctrine ORM自定义JoinColumn关联查询问题

核心ORM关联实现 #

在ChatPartner实体中，通过@OneToOne结合@JoinFormula实现双向聊天消息的匹配，并获取最新的一条消息：

// src/Entity/ChatPartner.php
namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;
use ApiPlatform\Metadata\ApiResource;
use ApiPlatform\Metadata\Get;
use ApiPlatform\Metadata\GetCollection;
use Symfony\Component\Serializer\Annotation\Groups;

#[ApiResource(
    operations: [new Get(), new GetCollection()],
    normalizationContext: ['groups' => ['chat_partner:read']],
    // 默认按最后消息时间倒序排序
    ordering: ['lastMessage.createdAt' => 'DESC'],
    // 允许客户端动态切换排序方向
    order: ['lastMessage.createdAt' => ['ASC', 'DESC']]
)]
#[ORM\Entity]
class ChatPartner
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer')]
    #[Groups(['chat_partner:read'])]
    private ?int $id = null;

    #[ORM\ManyToOne(targetEntity: User::class)]
    #[ORM\JoinColumn(nullable: false)]
    #[Groups(['chat_partner:read'])]
    private ?User $partner = null;

    #[ORM\ManyToOne(targetEntity: User::class)]
    #[ORM\JoinColumn(nullable: false)]
    #[Groups(['chat_partner:read'])]
    private ?User $contact = null;

    // 关联最新双向聊天消息，延迟加载避免性能损耗
    #[ORM\OneToOne(targetEntity: ChatMessage::class, fetch: 'EXTRA_LAZY')]
    #[ORM\JoinFormula(
        '(SELECT cm.id FROM chat_message cm WHERE 
            (cm.sender_id = partner_id AND cm.receiver_id = contact_id) 
            OR (cm.sender_id = contact_id AND cm.receiver_id = partner_id) 
            ORDER BY cm.created_at DESC LIMIT 1)'
    )]
    #[Groups(['chat_partner:read'])]
    private ?ChatMessage $lastMessage = null;

    // 省略getter、setter方法
}

关键说明 #

• @JoinFormula中的SQL直接针对数据库表/字段编写（而非实体属性），确保Doctrine能正确解析关联条件
• 使用fetch: 'EXTRA_LAZY'实现延迟加载，仅当访问lastMessage属性时才执行查询
• 子查询通过ORDER BY cm.created_at DESC LIMIT 1确保获取最新的一条消息

ApiPlatform排序支持 #

在ChatPartner的#[ApiResource]注解中配置：

• ordering：设置默认排序规则（按最后消息时间倒序）
• order：声明允许客户端动态排序的字段，支持ASC/DESC两种方向

客户端可通过请求参数切换排序，例如：

GET /api/chat_partners?order[lastMessage.createdAt]=asc

Mercure更新适配 #

在ChatMessage实体中配置Mercure推送，确保新消息发送时，对应的ChatPartner订阅者能收到更新：

// src/Entity/ChatMessage.php
namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;
use ApiPlatform\Metadata\ApiResource;
use ApiPlatform\Metadata\Post;
use ApiPlatform\Symfony\Mercure\UpdateMercureHub;
use Symfony\Component\Serializer\Annotation\Groups;

#[ApiResource(
    operations: [
        new Post(
            mercure: new UpdateMercureHub(
                // 为聊天双方生成对称主题，确保双向ChatPartner都能收到推送
                topics: [
                    'chat_partner/{sender.id}-{receiver.id}',
                    'chat_partner/{receiver.id}-{sender.id}',
                ]
            )
        )
    ],
    normalizationContext: ['groups' => ['chat_message:read', 'chat_partner:read']]
)]
#[ORM\Entity]
class ChatMessage
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer')]
    #[Groups(['chat_message:read', 'chat_partner:read'])]
    private ?int $id = null;

    #[ORM\ManyToOne(targetEntity: User::class)]
    #[ORM\JoinColumn(nullable: false)]
    #[Groups(['chat_message:read'])]
    private ?User $sender = null;

    #[ORM\ManyToOne(targetEntity: User::class)]
    #[ORM\JoinColumn(nullable: false)]
    #[Groups(['chat_message:read'])]
    private ?User $receiver = null;

    #[ORM\Column(type: 'text')]
    #[Groups(['chat_message:read', 'chat_partner:read'])]
    private ?string $content = null;

    #[ORM\Column(type: 'datetime_immutable')]
    #[Groups(['chat_message:read', 'chat_partner:read'])]
    private ?\DateTimeImmutable $createdAt = null;

    public function __construct()
    {
        $this->createdAt = new \DateTimeImmutable();
    }

    // 省略getter、setter方法
}

关键说明 #

• 为每条新消息生成两个对称主题，覆盖聊天双方的ChatPartner场景
• 确保ChatMessage的序列化组包含chat_partner:read，这样推送内容能被ChatPartner的客户端正确解析

注意事项 #

1. 需确保数据库表名、字段名与@JoinFormula中的SQL完全匹配（可通过Doctrine的schema:validate命令验证）
2. 若需全局预加载lastMessage，可将fetch改为EAGER，但需注意性能影响
3. 若数据库性能敏感，可考虑为chat_message表的sender_id、receiver_id、created_at字段创建联合索引，优化子查询速度

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