Kysely•8mo ago

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

4 Replies

thelinuxlich•8mo ago

Maybe use a window function?

AdophilusOP•8mo ago

How can I do that? Because I had to separate them into 2 queries One to get the data And the other to get the count

Igal•8mo ago

It requires some smelly hackery, if you want a single count (not repeated per row)

thelinuxlich•8mo ago

not repeated per row would be a CTE

Gaming

Programming

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