Symfony中Doctrine Query Builder左连接JSON列关联表的问题

问题原因 #

你使用的连接条件carImages.uuid IN (c.image_uuids)无法正确匹配JSON数组——数据库会把c.image_uuids视为普通字符串而非可遍历的数组，导致关联条件不成立，因此carImages返回空。

解决方案 #

根据你使用的数据库类型，调整连接条件：

情况1：MySQL/MariaDB #

使用JSON_CONTAINS函数判断image的uuid是否存在于cars的JSON数组中，同时用JSON_QUOTE将uuid转为JSON格式的字符串（匹配数组内的格式）：

$queryBuilder = $this->getEntityManager()->getConnection()->createQueryBuilder();
$queryBuilder
    ->select('c.id, c.image_uuids, carImages.id AS image_id, carImages.uuid, carImages.color')
    ->from('cars', 'c')
    ->leftJoin(
        'images',
        'carImages',
        'JSON_CONTAINS(c.image_uuids, JSON_QUOTE(carImages.uuid))'
    );

$resultSet = $queryBuilder->executeQuery();
$rawResults = $resultSet->fetchAllAssociative();

情况2：PostgreSQL #

将JSON数组转为uuid数组，用ANY操作符匹配：

$queryBuilder = $this->getEntityManager()->getConnection()->createQueryBuilder();
$queryBuilder
    ->select('c.id, c.image_uuids, carImages.id AS image_id, carImages.uuid, carImages.color')
    ->from('cars', 'c')
    ->leftJoin(
        'images',
        'carImages',
        'carImages.uuid = ANY(c.image_uuids::uuid[])'
    );

$resultSet = $queryBuilder->executeQuery();
$rawResults = $resultSet->fetchAllAssociative();

结果分组处理 #

执行查询后，每条car会对应多条image记录（每个匹配的image一条），可以在PHP中分组整理成你需要的结构：

$groupedCars = [];
foreach ($rawResults as $row) {
    $carId = $row['id'];
    if (!isset($groupedCars[$carId])) {
        $groupedCars[$carId] = [
            'id' => $carId,
            'image_uuids' => json_decode($row['image_uuids'], true),
            'carImages' => []
        ];
    }
    // 仅当存在关联image时添加
    if (!empty($row['image_id'])) {
        $groupedCars[$carId]['carImages'][] = [
            'id' => $row['image_id'],
            'uuid' => $row['uuid'],
            'color' => $row['color']
        ];
    }
}
// 转为索引数组
$finalCars = array_values($groupedCars);

注意事项 #

• 避免用select('*')，因为两张表都有id列会导致字段覆盖，建议明确指定需要的列并给重复字段加别名（比如carImages.id AS image_id）。
• 如果你的数据库是其他类型（比如SQLite），需要对应调整JSON相关的函数语法。

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