Laravel多关联参数Query Builder构建异常：SQL转写后统计结果错误

Let's break down the issue and fix your query step by step.

The Root Problem #

Your original SQL places tags.grupo = 'area' in the WHERE clause to filter the main tags table. But in your Query Builder code, you incorrectly moved this condition inside the LEFT JOIN closure. This meant you weren't filtering which tags rows are included in the query—you were only limiting which planificacion_info rows get joined. Since it's a LEFT JOIN, all tags rows (including those that don't match grupo='area') were still returned, and their associated planificacion_info data was null, leading to all aggregate counts showing zero.

Corrected Query Builder Code #

Here's the fixed version, with the misplaced condition moved to the proper spot and a small type correction:

$areas = \DB::table('tags')
    ->leftJoin('planificacion_info', function($query) use($fecha){
        $query->on('planificacion_info.id_area', '=', 'tags.id_tag')
            ->where('tags.estado', '=', true) // Use boolean true instead of string 'true' to match raw SQL
            ->where('planificacion_info.estado', '=', true)
            ->where(DB::raw('planificacion_info.created_at::Date'), '=', $fecha);
    })
    ->where('tags.grupo', '=', 'area') // This condition belongs in the WHERE clause, not JOIN ON
    ->selectRaw("
        tags.desc as areas,
        COUNT(planificacion_info.id_area) as cantidad_intervenciones,
        SUM(CASE WHEN planificacion_info.tipo_geometria = 'Polygon' AND planificacion_info.estado = true THEN 1 ELSE 0 END) as poligonos,
        SUM(CASE WHEN planificacion_info.tipo_geometria = 'LineString' AND planificacion_info.estado = true THEN 1 ELSE 0 END) as lineas,
        SUM(CASE WHEN planificacion_info.tipo_geometria = 'Point' AND planificacion_info.estado = true THEN 1 ELSE 0 END) as puntos,
        MIN(planificacion_info.created_at::time) as hora
    ")
    ->groupBy('tags.desc')
    ->get(); // Don't forget to call get() to execute the query and retrieve results

Key Fixes Explained #

1. Moved tags.grupo condition: By shifting ->where('tags.grupo', '=', 'area') outside the leftJoin closure, we ensure only tags rows with grupo='area' are included in the query—matching exactly what your original SQL does.
2. Fixed boolean comparison: Changed 'true' (string) to true (boolean) in the estado checks to align with the raw SQL's boolean literal, avoiding potential type mismatch issues.
3. Added ->get(): Without this method call, you're only building the query, not executing it to fetch actual results.

Why This Works #

Now the query behaves identically to your original SQL:

• It filters tags to only include rows where grupo='area'
• Performs a left join to planificacion_info using the correct association and additional join conditions
• Calculates aggregates based on matched planificacion_info rows, returning valid counts instead of zeros

内容的提问来源于stack exchange，提问作者Matías Ramírez