struggling to convert raw query to drizzle

[samadadi]

Hi. I am struggling to convert this raw query to drizzle. Please help me.
select (select count(*) from "Action") as total , (select json_agg(data) from (select "id", "name", "code" from "Action" limit 20 offset 20) as data) as rows

[ghost]

You can write this using With Clause

const cte = db.$with('cte').as(db.select({ ...Action, total: sql.raw(`count(*) over() as total`) }).from(Action).limit(20).offset(20));
    const result = await db.with(cte).select({
        rows: sql.raw(`array_agg(jsonb_build_object('id', id, 'name', name, 'code', code))`),
        total: sql.raw(`max(total)`)
    }).from(cte);

[eternity4318]

Where are you getting the CTE in .from(cte) from?

I'm trying to build my query and I'm starting by making the CTE the SQL query is

WITH generic_categories AS (
    SELECT unnest(ARRAY[]::int[]) as category_id  
),
location_categories_filter AS (
    SELECT unnest(ARRAY[]::int[]) as category_id  
),
date_categories_filter AS (
    SELECT unnest(ARRAY[9]) as category_id  
),

so far i came up with

function createCategoryCTE(array: number[], cteName: string) {
  let arrayString;

  if (array.length > 0) {
    arrayString = `ARRAY[${array.join(", ")}]`;
  } else {
    arrayString = "ARRAY[]::int[]";
  }

  return db.$with(cteName).as(
    db
      .select({ category_id: sql`unnest(${sql.raw(arrayString)})` })
      .from(dateCategories),
  );
}

but drizzle wont allow an empty from