path: root/src/app/db/helpers.js

/**
 * 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("");