如何用SqlKata库实现PostgreSQL指定交叉连接查询？

原SQL语句：

select rt.id as report_template_id, 
       string_agg(rtb.code, ',' order by b.idx) as aggregated_code
from report_template rt
  cross join unnest(rt.template_blocks_id) with ordinality as b(template_id, idx)
  join report_template_block rtb on rtb.id = b.template_id
group by rt.id
order by rt.id;

对应的SqlKata实现代码如下：

var query = new Query("report_template as rt")
    // 处理cross join unnest with ordinality逻辑
    .JoinRaw("cross join unnest(rt.template_blocks_id) with ordinality as b(template_id, idx)")
    // 关联report_template_block表
    .Join("report_template_block as rtb", "rtb.id", "b.template_id")
    // 指定查询字段，用Raw保留带排序的string_agg语法
    .Select(
        "rt.id as report_template_id",
        Raw("string_agg(rtb.code, ',' order by b.idx) as aggregated_code")
    )
    // 按模板ID分组
    .GroupBy("rt.id")
    // 按模板ID排序
    .OrderBy("rt.id");

关键实现说明： #

• JoinRaw处理PostgreSQL特定关联：由于SqlKata未直接封装unnest with ordinality语法，直接用JoinRaw写入原生SQL片段完成cross join逻辑。
• Raw处理带排序的聚合函数：string_agg中内嵌的order by b.idx是PostgreSQL专属语法，需要用Raw函数完整保留该表达式。
• 表关联、分组、排序等常规逻辑均使用SqlKata标准方法实现。

内容的提问来源于stack exchange，提问作者Максим Белов