Getting type from a CTE

import { eq, sql } from "drizzle-orm";
import { db } from "@/db";
import { permissionTable, userTable } from "@/db/schema";

import type { Permissions } from "@/db/queries";

export async function getUsers(limit: number = 20, offset: number = 0) {
  const usersQuery = db.$with("users").as(
    db
      .select({
        active: userTable.active,
        email: userTable.email,
        id: userTable.id,
        permissions:
          sql<Permissions>`json_agg(${permissionTable.permission})`.as(
            "permissions"
          ),
        role: userTable.role,
      })
      .from(userTable)
      .leftJoin(permissionTable, eq(userTable.id, permissionTable.userId))
      .groupBy(userTable.active, userTable.email, userTable.id, userTable.role)
      .limit(limit)
      .offset(offset)
  );

  return await db
    .with(usersQuery)
    .select({
      count: db.$count(userTable),
      users: sql`json_agg(${usersQuery})`,
    })
    .from(usersQuery);
}

import { eq, sql } from "drizzle-orm";
import { db } from "@/db";
import { permissionTable, userTable } from "@/db/schema";

import type { Permissions } from "@/db/queries";

export async function getUsers(limit: number = 20, offset: number = 0) {
  const usersQuery = db.$with("users").as(
    db
      .select({
        active: userTable.active,
        email: userTable.email,
        id: userTable.id,
        permissions:
          sql<Permissions>`json_agg(${permissionTable.permission})`.as(
            "permissions"
          ),
        role: userTable.role,
      })
      .from(userTable)
      .leftJoin(permissionTable, eq(userTable.id, permissionTable.userId))
      .groupBy(userTable.active, userTable.email, userTable.id, userTable.role)
      .limit(limit)
      .offset(offset)
  );

  return await db
    .with(usersQuery)
    .select({
      count: db.$count(userTable),
      users: sql`json_agg(${usersQuery})`,
    })
    .from(usersQuery);
}