feat: pagination count add where condition #20

[jobyjames1]

This PR introduces the capability to apply a WHERE condition in the row count calculation of the pagination function. This enhancement allows for more refined data retrieval by filtering records based on specified conditions, improving the flexibility and efficiency of pagination when dealing with large datasets.

• Updated Pagination Function: Added support for a whereSQL parameter in the pagination function configuration. The whereSQL parameter accepts an array of strings, where each string represents a field to be used in the WHERE clause.
• Conditional SQL Generation: Modified the countSQL query to include the WHERE clause only if whereSQL is specified and contains at least one field. This condition dynamically adds the WHEREclause and constructs the appropriate SQL statement.
• SQL Parameterization: The fields specified in whereSQL are mapped to variables to ensure that the SQL query is constructed safely, using placeholders for parameter values.
• Updated Row Count and Pagination Variables: countSQL is used to set the total number of rows based on the filtered query, which is then utilized to calculate pagination variables such as limit, offset, total_pages, and current_page.

const pagination = this.pagination({
  tableOrViewName: 'view_name', 
  whereSQL: ["condition_field"], 
});

Key Changes in pagination Function

const countSQL = config.countSQL
  ? config.countSQL
  : this.SQL`SELECT COUNT(*) FROM ${config.tableOrViewName}${
    config.whereSQL && config.whereSQL.length > 0
      ? ` WHERE ${
        config.whereSQL.map((qp) => `${n(qp)} =${$(qp)}`).join(" AND ")
      }`
      : ``
  }`;