Simplify everything with Postgres and Javascript - PL/v8

plv8-base.sql

-- eg SELECT sort_order('-relevance', ['title', 'updated_at', 'relevance'])
-- Optionally specify +/- prefix for sort field. Invalid sort field uses first in array
CREATE OR REPLACE FUNCTION sort_order(_field text, _valid text[]) RETURNS json AS
$$
  _field = decodeURIComponent(_field);
  var directions = { ' ': 'ASC', '-': 'DESC' }; // + becomes space
  var direction = directions[_field[0]];
  if(direction) { _field = _field.slice(1) } else { direction = directions['-']; };
  if(_valid.indexOf(_field) < 0) _field = _valid[0];
  return { field: _field, direction: direction };
$$
LANGUAGE plv8;

-- Ensure sane pagination parameters
CREATE OR REPLACE FUNCTION pagination(_page int, _page_size int) RETURNS json AS
$$
  _page = Math.max(1, _page);  
  _page_size = Math.max(Math.min(_page_size, 100), 5); // Max 100, min 5
  _offset = _page * _page_size - _page_size; // Do not care if offset is beyond the end
  return { page: _page, pageSize: _page_size, offset: _offset };
$$
LANGUAGE plv8;

-- Make user supplied keywords appropriate for TSearch eg "purification -water" -> "purification & !water"
CREATE OR REPLACE FUNCTION keywords(_keywords text = '', _type char = '&') RETURNS text AS
$$
  _keywords = decodeURIComponent(_keywords);
  return _keywords.toLowerCase().replace(/\-+/, '!').match(/!{0,1}\w+(:[a|b|c|d]){0,1}/g).join(' ' + _type + ' ');
$$
LANGUAGE plv8;

-- Simple query builder for handling count, retrieve and TSearch highlighting
CREATE OR REPLACE FUNCTION sql(_q json, _count boolean = false) RETURNS text AS
$$
  var q = 'SELECT ' + (_count ? 'count(*)' : _q.select.join(', '));
  q += ' FROM ' + _q.from.join(', ');
  q += ' WHERE ' + _q.where.join(' AND ');
  if(!_count) {
    // Stuff only for the row retrieval query
    q += ' ORDER BY ' + _q.order.field + ' ' + _q.order.direction;
    q += ' OFFSET ' + _q.offset;
    q += ' LIMIT ' + _q.limit;
    // The user provided keywords, and fields to highlight have been specified, so perform the main
    // query as a subselect to only perform highlighting on the final LIMITed rows
    if(_q.keywords && _q.highlight) q = 'SELECT ' + _q.highlight + ' FROM (' + q + ') AS rows';
  }
  return q;
$$
LANGUAGE plv8;

plv8-news.sql

-- Main "news" results list for /news/ url
-- eg SELECT news(4, 20, 'relevance', 'water purification');
CREATE OR REPLACE FUNCTION news(_page int = 1, _page_size int = 20, _order text = '-updated_at', _keywords text = '') RETURNS json AS
$$
  _keywords = _keywords || ''; // Ensure not undefined
  var params = [];
  var data = {
    paginate: plv8.execute("SELECT pagination($1, $2)", [_page, _page_size])[0].pagination
  };

  // The base retrieval
  var q = {
    select: ['n.id, ns.type, news_source_id, title, slug, image, intro, n.created_at, n.updated_at'],
    from: ['news n, news_sources ns'],
    where: ['ns.id = n.news_source_id'],
    offset: data.paginate.offset,
    limit: data.paginate.pageSize
  };

  if(_keywords) {
    q.keywords = plv8.execute("SELECT keywords($1)", [_keywords])[0].keywords;
    q.select.push('q');
    params.push(q.keywords);
    q.from.push("to_tsquery('english', $" + params.length + ") as q");
    q.where.push("tsv @@ q");
    q.order = plv8.execute("SELECT sort_order($1, '{relevance,updated_at}')", [_order])[0].sort_order;
  }
  else {
    q.order = plv8.execute("SELECT sort_order($1, '{updated_at}')", [_order])[0].sort_order;
  }

  var countSQL = plv8.execute("SELECT sql($1, true)", [q])[0].sql;

  // Do this after getting the count SQL to not pointlessly rank or highlight on a count query
  if(q.keywords && (q.order.field == 'relevance')) {
    q.highlight = "id, type, news_source_id, title AS raw_title, ts_headline('english', title, q, 'HighlightAll=FALSE') AS title, slug, image, created_at, updated_at, ts_headline('english', intro, q, 'MaxWords=20, MinWords=10, HighlightAll=FALSE') AS intro";
    q.select.push('ts_rank_cd(tsv, q) AS relevance');
  }

  data.paginate.total = plv8.execute(countSQL, params)[0].count;
  data.order = q.order;
  if(data.paginate.offset < data.paginate.total) {
    var rowsSQL = plv8.execute("SELECT sql($1)", [q])[0].sql;
    var rows = plv8.execute(rowsSQL, params);
    // Show the size before the actual rows, saves lots of scrolling during development
    data.paginate.size = rows.length;
    data.rows = rows;
  }
  return data;
$$
LANGUAGE plv8;