How can i make raw cross join with SqlKata library

Question

I’m use PostgreSQL with C# with SqlKata library. How can I make such query with SqlKata library?

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;

Answer 1

You can use FromRaw to achieve that

var query = new Query().FromRaw("report_template rt cross join unnest(rt.template_blocks_id) with ordinality as b(template_id, idx)")
  .Join("report_template_block as rtb", "rtb.id", "b.template_id")
  .GroupBy("rt.id")
  .OrderBy("rt.id");

check the example on SqlKata Playground

Very similar to this one SQLKata Update Query Including FromRaw Statement