Is it possible to get the total count while fetching rows in a single query?

I have a query which looks like this:

const { items, count } = await db
  .selectFrom(eb => eb
    .selectFrom("ads")
    .selectAll()
    .$if(filters?.is_active !== undefined, eb => eb.where("is_active", "=", filters?.is_active as AdsTable["is_active"]))
    .$if(filters?.is_expired !== undefined, eb => eb.where(sql`expires > now()`))
    .offset((paginationOptions.page - 1) * paginationOptions.perPage)
    .limit(paginationOptions.perPage)
    .as("items"))
  .select(() => [
    sql<number>`cast(count(1) as int)`.as("count"),
    sql<Selectable<AdsTable>[]>`coalesce(json_agg(items), '[]')`.as("items")
  ])
  .executeTakeFirstOrThrow()

const { items, count } = await db
  .selectFrom(eb => eb
    .selectFrom("ads")
    .selectAll()
    .$if(filters?.is_active !== undefined, eb => eb.where("is_active", "=", filters?.is_active as AdsTable["is_active"]))
    .$if(filters?.is_expired !== undefined, eb => eb.where(sql`expires > now()`))
    .offset((paginationOptions.page - 1) * paginationOptions.perPage)
    .limit(paginationOptions.perPage)
    .as("items"))
  .select(() => [
    sql<number>`cast(count(1) as int)`.as("count"),
    sql<Selectable<AdsTable>[]>`coalesce(json_agg(items), '[]')`.as("items")
  ])
  .executeTakeFirstOrThrow()

I noticed that the 'count' only counts the result of the query and not all the rows in the table. Is it possible to make it to count all the rows in the table?

I'm using PG

Kysely•2y ago•

8 replies

Adophilus

Is it possible to get the total count while fetching rows in a single query?

I have a query which looks like this:

const { items, count } = await db
  .selectFrom(eb => eb
    .selectFrom("ads")
    .selectAll()
    .$if(filters?.is_active !== undefined, eb => eb.where("is_active", "=", filters?.is_active as AdsTable["is_active"]))
    .$if(filters?.is_expired !== undefined, eb => eb.where(sql`expires > now()`))
    .offset((paginationOptions.page - 1) * paginationOptions.perPage)
    .limit(paginationOptions.perPage)
    .as("items"))
  .select(() => [
    sql<number>`cast(count(1) as int)`.as("count"),
    sql<Selectable<AdsTable>[]>`coalesce(json_agg(items), '[]')`.as("items")
  ])
  .executeTakeFirstOrThrow()

const { items, count } = await db
  .selectFrom(eb => eb
    .selectFrom("ads")
    .selectAll()
    .$if(filters?.is_active !== undefined, eb => eb.where("is_active", "=", filters?.is_active as AdsTable["is_active"]))
    .$if(filters?.is_expired !== undefined, eb => eb.where(sql`expires > now()`))
    .offset((paginationOptions.page - 1) * paginationOptions.perPage)
    .limit(paginationOptions.perPage)
    .as("items"))
  .select(() => [
    sql<number>`cast(count(1) as int)`.as("count"),
    sql<Selectable<AdsTable>[]>`coalesce(json_agg(items), '[]')`.as("items")
  ])
  .executeTakeFirstOrThrow()

I noticed that the 'count' only counts the result of the query and not all the rows in the table. Is it possible to make it to count all the rows in the table?

I'm using PG

Is it possible to get the total count while fetching rows in a single query?

Similar Threads

Is it possible to get the total count while fetching rows in a single query?

Similar Threads