Create pagination using KnexJS + PG

Question

What would be the most optimal way to create pagination using knexjs? I sincerily have no idea how to do pagination in general. Below my code:

router.get("/", (req, res) => {
  db.select("*")
    .from("site.site_product")
    .offset(0)
    .limit(10)
    .then(data => {
      res.render("inventory/site_product", { data: data });
    });
});

Any help will be appreciated.

Thank you

Answer 1

Try This

router.get("/", (req, res) => {
    var reqData = req.query;
    var pagination = {};
    var per_page = reqData.per_page || 10;
    var page = reqData.current_page || 1;
    if (page < 1) page = 1;
    var offset = (page - 1) * per_page;
    return Promise.all([
        db.count('* as count').from("site.site_product").first(),
        db.select("*").from("site.site_product").offset(offset).limit(per_page)
    ]).then(([total, rows]) => {
        var count = total.count;
        var rows = rows;
        pagination.total = count;
        pagination.per_page = per_page;
        pagination.offset = offset;
        pagination.to = offset + rows.length;
        pagination.last_page = Math.ceil(count / per_page);
        pagination.current_page = page;
        pagination.from = offset;
        pagination.data = rows;
        res.render("inventory/site_product", {
            data: pagination
        });
    });
});