Server-Side Pagination With PostgreSQL

Community Show & Tell
, , , ,
1

Hello,

I wanted to share a BASIC JS function that transforms a Table (New) filter stack into a WHERE statement for PostgreSQL with prepared statements disabled. This is needed when you switch from Client-Side pagination to Server-Side pagination

Add the following to your Preloaded JS and then use it in your Queries.

// define a filter stack to postgresql function
function filterToWhere(stack) {
  if (!stack) {
    return "true";  // empty stack
  }
  
  // handle group
  if (stack.hasOwnProperty("filters")) {
    if (stack.filters.length == 0 ) {
      return "true";  // empty filters
    }
    
    return `(${stack.filters.map(f => filterToWhere(f)).filter(f => !!f).join(` ${stack.operator} `)})`
  }
  
  // handle value
  let value = null;
  if (typeof stack.value === "string") {
    value = stack.value;
    let d = new Date(value);
    if (!isNaN(d)) {
      value = d.toISOString();
    }
    value = `'${value}'`;
    
  } else if (value == null) {
    value = null;
  } else {
    value = stack.value.toString();
  }
  
  // handle operator
  let stmt = null;
  let op = null;
  switch (stack.operator) {
    case "is":
      op = "=";
      break;
    case "isNot":
      op = "!=";
      break;
    case "isEmpty":
      stmt = `${stack.columnId} IS NULL`;
      break;
    case "isNotEmpty":
      stmt = `${stack.columnId} IS NOT NULL`;
      break;
    case "includes":
      stmt = `LOWER(${stack.columnId}) LIKE '%${stack.value.toLowerCase()}%'`;
      break;
    case "doesNotInclude":
      stmt = `LOWER(${stack.columnId}) NOT LIKE '%${stack.value.toLowerCase()}%'`;
      break;
    case "isTrue":
      stmt = `${stack.columnId} IS TRUE`;
      break;
    case "isFalse":
      stmt = `${stack.columnId} IS FALSE`;
      break;
    case "isAfter":
      op = ">";
      break;
    case "isBefore":
      op = "<";
      break;
    default:
      op = stack.operator;
      break;
  }
  
  // handle easy stmts
  if (stmt === null && op !== null) {
    stmt = `${stack.columnId} ${op} ${value}`;
  }
  return stmt;
}

Here is an example of using it in a PostgreSQL query and the following total query.

SELECT *
FROM (
  SELECT
     col1,
     col2,
     (col1 || ' ' || col2) AS derived_col
  FROM my_table
) a
WHERE {{filterToWhere(table.filterStack)}}
ORDER BY {{[table.sortArray].flatMap(a => !a || a.length == 0 ? ["id DESC"] : [a.map(aa => `${aa.columnId} ${aa.direction}`).join(",")])[0]}}
LIMIT {{table.pagination.pageSize}}
OFFSET {{table.pagination.currentPage}}

Total query

SELECT
   COUNT(1) AS total
FROM (
  SELECT
     col1,
     col2,
     (col1 || ' ' || col2) AS derived_col
  FROM my_table
) a
WHERE {{filterToWhere(table.filterStack)}}
4 Likes
2

Awesome! Thanks for sharing!

3

Need to make an edit here...

In the OFFSET calculation, you need to multiply the page size by the current page.

OFFSET {{table.pagination.currentPage * table.pagination.pageSize}}

Sorry about that!

1 Like