Laravel Query Builder查询结果不符预期，如何修正？

我在GraphQL查询中使用Laravel Query Builder编写了如下查询：

return LearningPath::query()
            ->selectRaw('learning_paths.id,learning_paths.name, count(*) as completed_nodes')
            ->selectRaw('(SELECT count(*) FROM learning_path_levels WHERE learning_path_levels.learning_path_id = learning_paths.id) as total_levels')
            ->selectRaw('(SELECT count(*) FROM learning_path_level_nodes WHERE learning_path_level_nodes.learning_path_level_id = learning_path_levels.id) as total_nodes')
            ->selectRaw('(count(*) * 100) / (SELECT count(*) FROM learning_path_level_nodes WHERE learning_path_level_nodes.learning_path_level_id = learning_path_levels.id) as percentage')
            ->join('learning_path_levels', 'learning_path_levels.learning_path_id', '=', 'learning_paths.id')
            ->join('learning_path_level_nodes', 'learning_path_level_nodes.learning_path_level_id', '=', 'learning_path_levels.id')
            ->leftJoin('learning_path_node_users', 'learning_path_node_users.learning_path_level_node_id', '=', 'learning_path_level_nodes.id')
            ->where('learning_path_node_users.user_id', auth()->id())
            ->orWhereNull('learning_path_node_users.user_id')
            ->groupBy('learning_paths.id','learning_path_levels.id')
            ->get();

当前返回结果仅包含至少有一个已完成（is_successful字段为true）关卡的学习路径：

{
  "data": {
    "learning_path_completed_levels": [
      {
        "id": "1",
        "name": "Frontend",
        "completed_nodes": 1,
        "total_nodes": 3,
        "percentage": 33
      },
      {
        "id": "3",
        "name": "Devops",
        "completed_nodes": 5,
        "total_nodes": 7,
        "percentage": 71
      },
      {
        "id": "6",
        "name": "Project Manager",
        "completed_nodes": 3,
        "total_nodes": 6,
        "percentage": 50
      }
    ]
  }
}

但我需要返回所有学习路径，包括完成节点数为0的情况，期望结果如下：

{
  "data": {
    "learning_path_completed_levels": [
      {
        "id": "1",
        "name": "Frontend",
        "completed_nodes": 1,
        "total_nodes": 3,
        "percentage": 33
      },
      {
        "id": "3",
        "name": "Devops",
        "completed_nodes": 1,
        "total_nodes": 5,
        "percentage": 20
      },
      {
        "id": "4",
        "name": "QA",
        "completed_nodes": 0,
        "total_nodes": 1,
        "percentage": 0
      },
      {
        "id": "5",
        "name": "AI",
        "completed_nodes": 0,
        "total_nodes": 5,
        "percentage": 0
      },
      {
        "id": "6",
        "name": "Project Manager",
        "completed_nodes": 3,
        "total_nodes": 6,
        "percentage": 50
      }
    ]
  }
}

请问如何修改该查询以得到预期结果？

要返回所有学习路径，需要调整关联方式和统计逻辑，具体修改点如下：

1. 将内连接改为左连接：原查询中join('learning_path_levels')和join('learning_path_level_nodes')是内连接，会过滤掉没有层级或节点的学习路径，改为leftJoin可以保留所有主表（learning_paths）数据。

2. 修正已完成节点的统计逻辑：原count(*)会统计所有匹配行（包括未完成的），需要改为统计当前用户已成功完成的节点，同时把用户匹配条件放到左连接的on子句中，避免过滤主表数据。

3. 处理空值与除零问题：对于没有层级/节点的学习路径，total_nodes会为null，计算百分比时要避免除零错误，用COALESCE和NULLIF处理。

4. 调整分组逻辑：确保分组只按学习路径ID和名称分组，避免同一学习路径按层级拆分多条记录。

修改后的完整查询代码：

return LearningPath::query()
    ->selectRaw('
        learning_paths.id,
        learning_paths.name,
        COUNT(learning_path_node_users.id) as completed_nodes,
        COALESCE((SELECT COUNT(*) FROM learning_path_levels WHERE learning_path_levels.learning_path_id = learning_paths.id), 0) as total_levels,
        COALESCE((SELECT COUNT(*) FROM learning_path_level_nodes lpln 
                  JOIN learning_path_levels lpl ON lpln.learning_path_level_id = lpl.id 
                  WHERE lpl.learning_path_id = learning_paths.id), 0) as total_nodes
    ')
    ->selectRaw('
        CASE 
            WHEN COALESCE((SELECT COUNT(*) FROM learning_path_level_nodes lpln 
                          JOIN learning_path_levels lpl ON lpln.learning_path_level_id = lpl.id 
                          WHERE lpl.learning_path_id = learning_paths.id), 0) = 0 
            THEN 0 
            ELSE (COUNT(learning_path_node_users.id) * 100) / NULLIF((SELECT COUNT(*) FROM learning_path_level_nodes lpln 
                                                                     JOIN learning_path_levels lpl ON lpln.learning_path_level_id = lpl.id 
                                                                     WHERE lpl.learning_path_id = learning_paths.id), 0) 
        END as percentage
    ')
    ->leftJoin('learning_path_levels', 'learning_path_levels.learning_path_id', '=', 'learning_paths.id')
    ->leftJoin('learning_path_level_nodes', 'learning_path_level_nodes.learning_path_level_id', '=', 'learning_path_levels.id')
    ->leftJoin('learning_path_node_users', function ($join) {
        $join->on('learning_path_node_users.learning_path_level_node_id', '=', 'learning_path_level_nodes.id')
             ->where('learning_path_node_users.user_id', auth()->id())
             ->where('learning_path_node_users.is_successful', true);
    })
    ->groupBy('learning_paths.id', 'learning_paths.name')
    ->get();

关键说明： #

• 把用户和完成状态的条件移到leftJoin的on子句中，确保不过滤没有完成记录的学习路径。
• 使用COUNT(learning_path_node_users.id)替代count(*)，左连接后未完成的记录对应字段为null，count会忽略null，准确统计已完成节点数。
• 用COALESCE将total_levels和total_nodes的null值转为0，避免统计异常。
• 用CASE语句处理百分比计算的除零情况，当总节点数为0时直接返回0。

内容的提问来源于stack exchange，提问作者Jarliev Pérez