基于Doctrine实现多对多关系下按分类获取最新植物

正确的Doctrine QueryBuilder实现 #

你的核心需求是关联分类与最新创建的植物记录，以下是对应你原生SQL的QueryBuilder实现：

// 获取EntityManager（Repository中可直接调用$this->getEntityManager()）
$entityManager = $this->getEntityManager();

// 构建子查询：获取最新的1条植物记录
$latestPlantSubQuery = $entityManager->createQueryBuilder()
    ->select('p.id, p.name')
    ->from(Plant::class, 'p')
    ->orderBy('p.id', 'DESC')
    ->setMaxResults(1);

// 主查询：关联分类、中间表与最新植物
$queryBuilder = $entityManager->createQueryBuilder()
    ->select('c.id AS category_id, c.name, c.slug, lp.name AS plant_name')
    ->from(Category::class, 'c')
    ->leftJoin('c.plants', 'pc') // 需确保Category实体已定义与Plant的多对多关联，关联名对应中间表
    ->innerJoin(
        '(' . $latestPlantSubQuery->getDql() . ')',
        'lp',
        'WITH',
        'pc.id = lp.id'
    )
    ->groupBy('c.id, c.name, c.slug, lp.name')
    ->orderBy('c.id', 'ASC');

// 执行查询获取结果
$results = $queryBuilder->getQuery()->getResult();

关键注意事项 #

• 实体关联校验：确保Category实体中正确配置多对多关联，示例如下：

  // Category.php
  /**
   * @ORM\ManyToMany(targetEntity=Plant::class, inversedBy="categories")
   * @ORM\JoinTable(
   *     name="plant_category",
   *     joinColumns={@ORM\JoinColumn(name="category_id", referencedColumnName="id")},
   *     inverseJoinColumns={@ORM\JoinColumn(name="plant_id", referencedColumnName="id")}
   * )
   */
  private $plants;
  

• 子查询处理：Doctrine QueryBuilder不支持直接在JOIN中写入原生SQL子查询，需先构建子查询的DQL，再通过字符串拼接嵌入主查询
• 字段与分组匹配：GROUP BY需包含所有非聚合查询字段，避免SQL模式报错

原生SQL备选方案 #

如果DQL方式存在限制，可直接通过Doctrine执行你的原生SQL：

$sql = "SELECT category_id, c.name, c.slug, i.name AS plant_name
FROM category AS c
LEFT JOIN plant_category AS pc ON pc.category_id = c.id
INNER JOIN (SELECT id, name FROM plant ORDER BY id DESC LIMIT 1) AS i ON pc.plant_id = i.id
GROUP BY c.id, c.name, c.slug, i.name
ORDER BY c.id ASC";

$stmt = $this->getEntityManager()->getConnection()->prepare($sql);
$stmt->execute();
$results = $stmt->fetchAllAssociative();

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