Looking for a way to get a total count of items returned from a database query, for use in pagination with knex

Question

I am currently adding pagination to my back-end models for retrieving articles from a database using knex. The model has a limit and an offset, but i need to get hold of the total count of rows returned before limit and offset are applied.

I have considered using a separate model to get a count of the articles, but it seems unnecessary.

  return connection
    .select(
      "articles.author",
      "articles.title",
      "articles.article_id",
      "articles.topic",
      "articles.created_at",
      "articles.votes"
    )
    .count({ comment_count: "comments.article_id" })
    .from("articles")
    .leftJoin("comments", "articles.article_id", "comments.article_id")
    .groupBy("articles.article_id")
    .orderBy(sort_by || "created_at", order || "desc")
    .limit(limit || 10)
    .offset((p - 1) * (limit || 10))
    .modify(query => {
      if (username) query.where("articles.author", username);
      if (topic) query.where("articles.topic", topic);
    });
};```

Expect to add a total_count property

Answer 1

You do have to get a count seperately from the query you are executing. Get the count first, and then run the query again with the limit and offset to get the records

connection
.select(
  "articles.author",
  "articles.title",
  "articles.article_id",
  "articles.topic",
  "articles.created_at",
  "articles.votes"
)
.count({ comment_count: "comments.article_id" })
.from("articles")
.leftJoin("comments", "articles.article_id", "comments.article_id")
.groupBy("articles.article_id")
.orderBy(sort_by || "created_at", order || "desc")