将SQL查询转为Laravel Query Builder时遇报错问题求助

问题背景 #

需要将以下SQL查询转换为Laravel Query Builder代码：

SELECT * FROM (SELECT 'Meetings Offered' AS Source,
             'Meetings Chosen' AS Destination,
             COUNT(1) AS value,
             DATE (created_at) AS DATE
      FROM typeform_contacts
      WHERE client_signed = 1
      GROUP BY DATE (created_at)
      UNION
      SELECT 'Meetings Chosen' AS Source,
             '4A OR 4B' AS Destination,
             COUNT(1) AS Value,
             DATE (created_at) AS DATE
      FROM lawyer_opportunity_meetings
      WHERE contact_type = 'typeform'
      GROUP BY DATE (created_at)
      UNION
      SELECT '4A OR 4B' AS Source,
             '4B Get L times' AS Destination,
             COUNT(1) AS Value,
             DATE (date_added) AS DATE
      FROM dripfeed_targets
      WHERE feed_id in(360, 361)
      GROUP BY DATE (date_added)
      UNION
      SELECT '4B Get L times' AS Source,
             '4C - Meeting Set' AS Destination,
             COUNT(1) AS Value,
             DATE (date_added) AS DATE
      FROM dripfeed_targets
      WHERE feed_id in(361)
      GROUP BY DATE (date_added)
      UNION
      SELECT '4C - Meeting Set' AS Source,
             '4D Follow up' AS Destination,
             COUNT(1) AS Value,
             DATE (date_added) AS DATE
      FROM dripfeed_targets
      WHERE feed_id in(362)
      GROUP BY DATE (date_added)
      UNION
      SELECT '4D Follow up' AS Source,
             '' AS Destination,
             COUNT(1) AS Value,
             DATE (date_added) AS DATE
      FROM dripfeed_targets
      WHERE feed_id in(363)
      GROUP BY DATE (date_added)
) AS TEMP 
WHERE DATE BETWEEN '2023-1-1' AND '2023-1-30'

自行编写的Laravel代码运行时出现两个错误：

1. SQLSTATE[42703]: Undefined column: 7 ERROR: column "client_signed" does not exist
2. 单独执行生成的SQL时提示syntax error at or near "as"

错误分析 #

1. 字符串条件未正确转义：第二个union查询中，where('contact_type', 'typeform')生成的SQL未给typeform添加单引号，PostgreSQL将其识别为列名，引发语法错误，进而导致后续SQL解析失败。
2. 多余嵌套括号：Laravel原生fromSub处理多union查询时，生成的SQL包含多余的嵌套括号，不符合PostgreSQL的语法规范。
3. 列名大小写/存在性问题：client_signed列不存在的报错，需确认表结构中列名拼写正确，注意PostgreSQL对大小写敏感（若列名创建时用双引号包裹，查询时也需用双引号）。

修正后的代码 #

public function meetingSankey() {
    // 拆分每个UNION子查询，提升可读性
    $query1 = DB::table('typeform_contacts')
        ->selectRaw("'Meetings Offered' AS Source, 'Meetings Chosen' AS Destination, COUNT(1) AS value, DATE(created_at) AS DATE")
        ->where('client_signed', 1)
        // 若列名存在大小写问题，改用：whereRaw('"client_signed" = 1')
        ->groupByRaw('DATE(created_at)');

    $query2 = DB::table('lawyer_opportunity_meetings')
        ->selectRaw("'Meetings Chosen' AS Source, '4A OR 4B' AS Destination, COUNT(1) AS Value, DATE(created_at) AS DATE")
        ->where('contact_type', 'typeform')
        // 若仍存在字符串转义问题，改用：whereRaw("contact_type = 'typeform'")
        ->groupByRaw('DATE(created_at)');

    $query3 = DB::table('dripfeed_targets')
        ->selectRaw("'4A OR 4B' AS Source, '4B Get L times' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE")
        ->whereIn('feed_id', [360, 361])
        ->groupByRaw('DATE(date_added)');

    $query4 = DB::table('dripfeed_targets')
        ->selectRaw("'4B Get L times' AS Source, '4C - Meeting Set' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE")
        ->whereIn('feed_id', [361])
        ->groupByRaw('DATE(date_added)');

    $query5 = DB::table('dripfeed_targets')
        ->selectRaw("'4C - Meeting Set' AS Source, '4D Follow up' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE")
        ->whereIn('feed_id', [362])
        ->groupByRaw('DATE(date_added)');

    $query6 = DB::table('dripfeed_targets')
        ->selectRaw("'4D Follow up' AS Source, '' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE")
        ->whereIn('feed_id', [363])
        ->groupByRaw('DATE(date_added)');

    // 合并所有UNION查询，手动构建子查询避免多余括号
    $unionQuery = $query1->union($query2)->union($query3)->union($query4)->union($query5)->union($query6);
    
    $result = DB::table(DB::raw("({$unionQuery->toSql()}) as temp"))
        ->mergeBindings($unionQuery) // 绑定所有子查询的参数，防止SQL注入
        ->whereBetween('DATE', ['2023-01-01', Carbon::now()->toDateString()])
        ->get();

    return response()->json([
        'data' => $result,
        'status' => 'success'
    ], 200);
}

关键修正点 #

• 拆分每个UNION子查询为独立变量，便于维护和调试。
• 使用DB::raw手动包裹整个UNION查询，避免Laravel生成多余的嵌套括号，解决PostgreSQL语法错误。
• 通过mergeBindings绑定所有子查询的参数，确保安全性。
• 日期参数改用纯日期字符串，与原SQL逻辑保持一致，避免时间部分干扰。
• 针对列名大小写问题，提供了备选的whereRaw写法。

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