1 files changed, 248 insertions, 0 deletions
diff --git a/src/app/db/helpers.js b/src/app/db/helpers.js
new file mode 100644
index 0000000..0b19833
--- /dev/null
+++ b/src/app/db/helpers.js
@@ -0,0 +1,248 @@
+/**
+ * Helper functions for database queries.
+ * @module app/db/helpers
+ */
+
+import { capitalizeWord } from "app/utils/text_utils";
+
+/**
+ * Build an index query.
+ * @param {knex.builder}    builder       the Knex query builder
+ * @param {bookshelf.Model} Model         the affected model
+ * @param {Object}          query         the query parameters
+ * @param {Object}          paginate      pagination settings
+ * @param {User}            user          the request.user
+ * @param {function}        queryBuilder  optional, function to build the query
+ * @param {Object}          columns       valid columns on the model
+ * @returns {knex.builder}                the supplied Knex query builder object
+ */
+export function buildIndexQuery({
+  builder,
+  Model,
+  query,
+  paginate,
+  queryBuilder,
+  columns,
+}) {
+  const { offset, limit } = getOffsetAndLimit(query, paginate);
+  const { sortField, sortDirection, sortCount } = getSort(query, Model);
+
+  const tableName = Model.prototype.tableName;
+  const idAttribute = Model.prototype.idAttribute;
+  const sortTableName = tableName + "_" + sortField;
+  const sortOrderName = sortField + "_count";
+
+  // Get the filtered columns
+  let filters = getQueryFilters(query);
+
+  if (sortCount) {
+    // Sort by a pivot relation
+    builder.select("*", function () {
+      // Note: Knex syntax requires "this"
+      return this.from(sortTableName)
+        .count("*")
+        .whereRaw("?? = ??", [
+          `${sortTableName}.${idAttribute}`,
+          `${tableName}.${idAttribute}`,
+        ])
+        .as(sortOrderName);
+    });
+  }
+
+  // If there are filters, use the supplied query builder
+  if (Object.keys(filters).length > 0) {
+    if (queryBuilder) {
+      queryBuilder(builder, columns, filters);
+    } else {
+      builder.where(filters);
+    }
+  }
+
+  // Sorting
+  if (sortCount) {
+    builder.orderBy(sortOrderName, sortDirection);
+  } else {
+    builder.orderBy(sortField, sortDirection);
+  }
+
+  // Pagination
+  if (limit) {
+    builder.limit(limit);
+  }
+  if (offset) {
+    builder.offset(offset);
+  }
+
+  // console.log(builder)
+  return builder;
+}
+
+/**
+ * Build a count query.
+ * @param {knex.builder}    builder       the Knex query builder
+ * @param {Object}          query         the query parameters
+ * @param {User}            user          the request.user
+ * @param {function}        queryBuilder  optional, function to build the query
+ * @param {Object}          columns       valid columns on the model
+ * @returns {knex.builder}                the supplied Knex query builder object
+ */
+export function buildCountQuery({ builder, query, queryBuilder, columns }) {
+  let filters = getQueryFilters(query);
+
+  if (Object.keys(filters).length > 0) {
+    if (queryBuilder) {
+      queryBuilder(builder, columns, filters);
+    } else {
+      builder.where(filters);
+    }
+  }
+
+  return builder;
+}
+
+/**
+ * Build a pagination response for a count request.
+ * @param {number}          rowCount  the total number of response returned from count()
+ * @param {Object}          query     the query parameters
+ * @param {Object}          paginate  the service pagination
+ * @returns {knex.builder}            the response object
+ */
+export function buildPaginationResponse({ rowCount, query, paginate }) {
+  const { page, limit } = getOffsetAndLimit(query, paginate);
+  return {
+    rowCount: rowCount || 0,
+    page: page,
+    pageSize: limit,
+    pages: limit ? Math.ceil(rowCount / limit) : 1,
+  };
+}
+
+/**
+ * Query builder for index queries. Override in subclasses.
+ * @param  {knex.builder}  Knex query builder
+ * @param  {Object}        query parameters, minus offset/sort parameters
+ */
+export function indexQueryBuilder(builder, columns, filters) {
+  const validFilters = Object.keys(columns).reduce((valid, name) => {
+    if (name in filters) {
+      valid[name] = filters[name];
+    }
+    return valid;
+  }, {});
+  builder.where(validFilters);
+}
+
+/**
+ * Get the offset and limit for a given query.
+ * @param {Object} query                   the query object
+ * @param {number} query.offset            numeric offset into results
+ * @param {number} query.limit             maximum number of results to return
+ * @param {number} query.pageSize          size of a page of results (synonym for limit)
+ * @param {number} query.page              which page to return, zero-indexed
+ * @param {Object} paginate                pagination settings
+ * @param {number} paginate.pageSize       default page size
+ * @param {number} paginate.pageSizeLimit  maximum results to return, caps limit
+ * @return {Object}                        query offset, limit, and page
+ */
+export function getOffsetAndLimit(query, paginate) {
+  let { offset, limit, pageSize, page } = query;
+
+  page = parseInt(page) || 0;
+
+  if (paginate) {
+    limit = parseInt(limit) || parseInt(pageSize) || paginate.pageSize;
+    if (paginate.pageSizeLimit) {
+      limit = Math.min(limit, paginate.pageSizeLimit);
+    }
+  } else if (limit) {
+    limit = parseInt(limit);
+  } else {
+    limit = 0;
+  }
+
+  if (page) {
+    offset = page * limit;
+  } else if (offset) {
+    offset = parseInt(offset);
+  } else {
+    offset = 0;
+  }
+  return { offset, limit, page };
+}
+
+/**
+ * Get the sort options for a query.
+ * @param {Object}          query          the query object
+ * @param {string}          query.sort     field to sort by
+ * @param {string}          query.order    sort direction, can be asc or desc
+ * @param {bookshelf.Model} Model the model to query, used to get the primary key
+ */
+export function getSort(query, Model) {
+  let { sort, order } = query;
+  let sortParts, sortField, sortDirection;
+  let sortCount = false;
+  if (order) {
+    sortField = sort;
+    sortDirection = order;
+  } else {
+    sortParts = (sort || "id desc").split(" ", 1);
+    sortField = sortParts[0] || "id";
+    sortDirection = sortParts[1] || "desc";
+  }
+  if (sortField === "id") {
+    sortField = Model.prototype.idAttribute;
+  }
+  if (sortField.match("_count")) {
+    sortField = sortField.split("_count")[0];
+    sortCount = true;
+  }
+  return { sortField, sortDirection, sortCount };
+}
+
+/**
+ * Get the columns to filter by, without offset/sort options
+ * @param {Object}  query  the query object
+ * @return {Object}        fields unrelated to offset/sort
+ */
+export function getQueryFilters(query) {
+  // eslint-disable-next-line no-unused-vars
+  let { offset, limit, pageSize, page, sort, order, ...filters } = query;
+  return filters;
+}
+
+/**
+ * Load a table's columns from the database
+ * @param {bookshelf.Model} Model the model to query
+ * @return {Object} columns the columns
+ */
+export async function loadColumns(bookshelf, Model) {
+  if (!Model) {
+    return {};
+  }
+  const columns = await bookshelf.knex(Model.prototype.tableName).columnInfo();
+  return columns;
+}
+
+/**
+ * Filter the data in an object, leaving only prdetermined columns
+ * @param {Object} data          data to filter, typically a request.body
+ * @param {Object} columns       columns lookup returned from loadColumns
+ * @param {Array}  privateFields columns not to include in the returned data
+ * @return {Object} the filtered data
+ */
+export function reduceValidColumns(data, columns, privateFields = []) {
+  return Object.keys(columns).reduce((item, columnName) => {
+    if (columnName in data && !privateFields.includes(columnName)) {
+      item[columnName] = data[columnName];
+    }
+    return item;
+  }, {});
+}
+
+/**
+ * Convert a table name to a modelname (camel case, capitalized)
+ * @param  {string} tableName  the table name
+ * @return {string}            the model name
+ */
+export const tableNameToModelName = (tableName = "") =>
+  tableName.split("_").map(capitalizeWord).join("");