DT
Drizzle Team•5d ago
html_extraordinaire

Getting type from a CTE

I've got the following function: 
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);
}
I've been trying to get the type for the users through inference. I know how to do it with a regular query but Drizzle balks when I pull the query from within the CTE and attempt to infer its type. It will only work if I define the query within the CTE. What can I do in this situation?
1 Reply
html_extraordinaire
html_extraordinaireOP•5d ago
I don't want to manually type the users property. I know I can do this: 
sql<{ active: boolean; etc... }>`json_agg(${usersQuery})`
sql<{ active: boolean; etc... }>`json_agg(${usersQuery})`

Did you find this page helpful?