Feng Yu Posts - Answer Overflow

Feng Yu

•Created by Feng Yu on 4/2/2025 in #help

Magic sql does not add table name in query?

Here's the sample:

  const userSubQuery = sql<{ logo: string | null; email: string }>`
  (
    SELECT json_build_object(
      'logo', ${users.logo},
      'email', ${users.email}
    )
    FROM ${users}
    WHERE ${users.id} = ${orders.userId}
  )
  `.as("user");
db.select(...getTableColumns(orders), user: userSubQuery).from(orders).where(eq(orders.id, 2))

  const userSubQuery = sql<{ logo: string | null; email: string }>`
  (
    SELECT json_build_object(
      'logo', ${users.logo},
      'email', ${users.email}
    )
    FROM ${users}
    WHERE ${users.id} = ${orders.userId}
  )
  `.as("user");
db.select(...getTableColumns(orders), user: userSubQuery).from(orders).where(eq(orders.id, 2))

The generated SQL is:

Query: select "id", ... , "updated_at", "expired_at", 
  (
    SELECT json_build_object(
      'logo', "logo",
      'email', "email"
    )
    FROM "users"
    WHERE "id" = "user_id"
  )
   as "user" from "orders" where "orders"."id" = $1 -- params: [2]

Query: select "id", ... , "updated_at", "expired_at", 
  (
    SELECT json_build_object(
      'logo', "logo",
      'email', "email"
    )
    FROM "users"
    WHERE "id" = "user_id"
  )
   as "user" from "orders" where "orders"."id" = $1 -- params: [2]

You can see the ${users.logo} not convert to "users"."logo", ${users.id} not convert to "users"."id", which will cause some queries not get the correct result. How could I solve it? I really don't like the pure SQL like SELECT json_build_object('logo', u.logo) FROM users u because this lack of IDE support (variables trace). Thanks.

1 replies

DTDrizzle Team

•Created by Feng Yu on 3/26/2025 in #help

Is there any easy way to query both total count and pagination result?

The official doc shows a simple pagination query with dynamic query:

function withPagination<T extends PgSelect>(
    qb: T,
    page: number = 1,
    pageSize: number = 10,
) {
    return qb.limit(pageSize).offset((page - 1) * pageSize);
}

function withPagination<T extends PgSelect>(
    qb: T,
    page: number = 1,
    pageSize: number = 10,
) {
    return qb.limit(pageSize).offset((page - 1) * pageSize);
}

But how to also return the total count ?

function withPagination<T extends PgSelect>(
    qb: T,
    page: number = 1,
    pageSize: number = 10,
): {count: number, result: any}

function withPagination<T extends PgSelect>(
    qb: T,
    page: number = 1,
    pageSize: number = 10,
): {count: number, result: any}

I know Grails GORM has a PagedResultList wrapper to do so : https://docs.grails.org/latest/ref/Domain%20Classes/createCriteria.html When pass pagination query params it will query database twice, one is total count query, and another is the pagination result, and wrapper them into a PagedResultList class. Is there any way to do this in Drizzle ORM? Like:

function withPaginationAndCount<T extends PgSelect>(
    qb: T,
    page: number = 1,
    pageSize: number = 10,
): {count: number, result: any} {
  // how to rewrite like this?
  const count = qb.select({count: count()})...;
  count paginationResult = qb.limit(pageSize).offset((page - 1) * pageSize);
  return Promise.all([count, paginationResult]);
}

function withPaginationAndCount<T extends PgSelect>(
    qb: T,
    page: number = 1,
    pageSize: number = 10,
): {count: number, result: any} {
  // how to rewrite like this?
  const count = qb.select({count: count()})...;
  count paginationResult = qb.limit(pageSize).offset((page - 1) * pageSize);
  return Promise.all([count, paginationResult]);
}

Thanks.

2 replies

Gaming

Programming