3 files changed, 490 insertions, 0 deletions
diff --git a/src/app/db/service/pivot/helpers.js b/src/app/db/service/pivot/helpers.js
new file mode 100644
index 0000000..1d8f7f2
--- /dev/null
+++ b/src/app/db/service/pivot/helpers.js
@@ -0,0 +1,113 @@
+/**
+ * Pivot Table Helpers
+ * @module app/db/service/pivot/helpers
+ */
+
+import * as db from "app/db/query";
+
+/**
+ * Fetch a set of database objects from a pivot table via relations
+ * @param {bookshelf.Model} Model              the pivot table model to query
+ * @param {string}          parentIdAttribute  the parent ID attribute on the pivot
+ * @param {number}          parentId           the parent ID to query
+ * @param {string}          childIdAttribute   the child ID attribute on the pivot
+ * @param {number}          childId            the child ID to query
+ */
+export function indexPivotTable({
+  Model,
+  parentIdAttribute,
+  parentId,
+  childIdAttribute,
+  childId,
+}) {
+  return Model.query((builder) => {
+    builder.where(parentIdAttribute, parentId);
+    if (Array.isArray(childId)) {
+      builder.whereIn(childIdAttribute, childId);
+    } else {
+      builder.andWhere(childIdAttribute, childId);
+    }
+  }).fetchAll();
+}
+
+/**
+ * Get the parent, child, and pivot models from a service, as well as their ID attribute names
+ * @param  {Service} service  the pivot table service
+ * @return {object}           the models and ID attribute names
+ */
+export function getPivotModels(service) {
+  const {
+    parent,
+    Model,
+    ChildModel,
+    childRelation,
+    parentPivotRelation,
+    pivotChildRelation,
+  } = service.options;
+  const { Model: ParentModel } = service.options.parent;
+  const parentIdAttribute = ParentModel.prototype.idAttribute;
+  const pivotIdAttribute = Model.prototype.idAttribute;
+  const childIdAttribute = ChildModel.prototype.idAttribute;
+  const parentTableName = ParentModel.prototype.tableName;
+  const pivotTableName = Model.prototype.tableName;
+  const childTableName = ChildModel.prototype.tableName;
+  return {
+    parent,
+    Model,
+    ParentModel,
+    ChildModel,
+    parentTableName,
+    pivotTableName,
+    childTableName,
+    parentIdAttribute,
+    pivotIdAttribute,
+    childIdAttribute,
+    parentPivotRelation,
+    pivotChildRelation,
+    childRelation,
+  };
+}
+
+/**
+ * Create a single instance
+ * @param  {bookshelf.Model} options.Model     the model
+ * @param  {Object}          options.data      data to add
+ * @param  {Array}           options.instances fetched instances to dedupe
+ * @return {Object}                            result
+ */
+export async function handleCreateOne({ Model, data, instances }) {
+  if (instances.length) {
+    throw new Error("pivot relation already exists");
+  }
+  return await db.create({ Model, data });
+}
+
+/**
+ * Create multiple instances
+ * @param  {bookshelf.Model} options.Model             the model
+ * @param  {Object}          options.data              data to add
+ * @param  {Array}           options.instances         fetched instances to dedupe
+ * @param  {string}          options.childIdAttribute  the child ID, found on the body, which should point to an array
+ * @return {Object}                                    result
+ */
+export async function handleCreateMany({
+  Model,
+  data,
+  childIdAttribute,
+  instances,
+}) {
+  let { [childIdAttribute]: child_ids, ...columns } = data;
+  const matched = new Set(
+    instances.map((instance) => instance.get(childIdAttribute))
+  );
+  return await Promise.all(
+    Array.from(new Set(child_ids))
+      .filter((child_id) => !matched.has(child_id))
+      .map((child_id) =>
+        db.create({
+          Model,
+          data: { ...columns, [childIdAttribute]: child_id },
+        })
+      )
+  );
+}
diff --git a/src/app/db/service/pivot/index.js b/src/app/db/service/pivot/index.js
new file mode 100644
index 0000000..ded69b8
--- /dev/null
+++ b/src/app/db/service/pivot/index.js
@@ -0,0 +1,47 @@
+/**
+ * Pivot Table API Service
+ * @module app/db/service/pivot/index
+ */
+
+import Service from "app/db/service/base";
+import * as pivotMethods from "app/db/service/pivot/methods";
+import { loadColumns } from "app/db/helpers";
+
+/**
+ * Create a relational API service to access models via a pivot table.
+ * All options are the same as on a normal service, but with a few changes:
+ * @param {Model|string}  options.Model                    the Bookshelf pivot model
+ * @param {string}        options.ChildModel               the Bookshelf model inheriting from the pivot
+ * @param {string}        options.parentPivotRelation      relation method on the parent that will access the pivot
+ * @param {string}        options.pivotChildRelation       relation method on the pivot that will access the child
+ * @param {string}        options.childRelation            relation method on the parent that will access the children
+ * @return {Service} the service object
+ */
+export default async function PivotTableService(options) {
+  const { ChildModel, bookshelf } = options;
+
+  /** Locate the Model specified in the configuration */
+  options.ChildModel =
+    ChildModel && typeof ChildModel === "string"
+      ? bookshelf.model(ChildModel)
+      : ChildModel;
+
+  /** Due to the way English is inflected, sometimes these are the same and only one needs to be specified */
+  options.pivotChildRelation =
+    options.pivotChildRelation || options.childRelation;
+
+  options.pivotColumns =
+    ChildModel && (await loadColumns(bookshelf, ChildModel));
+
+  // /** Use the model to identify the service's resource name */
+  // options.parentResource = options.name || service.Model?.prototype.tableName;
+  // if (!service.resource) {
+  //   throw new Error("No name or model defined for resource");
+  // }
+
+  const service = await Service(options, pivotMethods);
+  service.type = "pivot";
+  service.ChildModel = options.ChildModel;
+
+  return service;
+}
diff --git a/src/app/db/service/pivot/methods.js b/src/app/db/service/pivot/methods.js
new file mode 100644
index 0000000..1f54345
--- /dev/null
+++ b/src/app/db/service/pivot/methods.js
@@ -0,0 +1,330 @@
+/**
+ * Pivot Table Service API Methods
+ * @module app/db/service/pivot/methods
+ */
+
+import * as db from "app/db/query";
+import {
+  buildPaginationResponse,
+  getOffsetAndLimit,
+  getSort,
+  getQueryFilters,
+  reduceValidColumns,
+  tableNameToModelName,
+} from "app/db/helpers";
+import {
+  indexPivotTable,
+  getPivotModels,
+  handleCreateOne,
+  handleCreateMany,
+} from "app/db/service/pivot/helpers";
+import { PERMISSIONS } from "app/constants";
+import debugModule from "debug";
+
+/**
+ * Debug logger
+ */
+const debug = debugModule("shoebox:service:pivot");
+
+/**
+ * API to index a model via a pivot table.  Allows pagination, filtering.
+ */
+export function index(service) {
+  const { queryBuilder } = service;
+  const { pivotColumns, paginate } = service.options;
+  const {
+    parent,
+    Model,
+    ChildModel,
+    pivotTableName,
+    childTableName,
+    parentIdAttribute,
+    childIdAttribute,
+    parentPivotRelation,
+    childRelation,
+  } = getPivotModels(service);
+
+  const pivotAttribute = Model.prototype.idAttribute.replace(/_id$/, "");
+  // console.log(pivotIdAttribute, pivotAttribute);
+
+  return async function indexPivotMiddleware(request, response, next) {
+    const { query } = request;
+    const withRelated = query.related ? query.related.split(",") : [];
+
+    const filters = getQueryFilters(query);
+    const hasFilters = Object.keys(filters).length > 0;
+    const { offset, limit } = getOffsetAndLimit(query, paginate);
+    const { sortField, sortDirection, sortCount } = getSort(query, ChildModel);
+
+    // Fetch the immediate parent of the pivot table based on the name of the parent resource.
+    // This instance is added to the `request.parents` object when performing the permissions check.
+    const parentInstance = request.parents[parent.resource];
+
+    // Fetch the children of the pivot table.
+    let childData;
+
+    if (sortCount) {
+      // Sort by the count of a secondary pivot relation on the child
+      const sortTableName = childTableName + "_" + sortField;
+      const sortOrderName = sortField + "_count";
+      const sortTableModel = request.bookshelf.model(
+        tableNameToModelName(childTableName + "_" + sortField)
+      );
+      if (!sortTableModel) {
+        return next(new Error("No such pivot table"));
+      }
+      let query = request.bookshelf.knex
+        .from(childTableName)
+        .select(
+          childTableName + ".*",
+          request.bookshelf
+            .knex(sortTableName)
+            .count("*")
+            .whereRaw("?? = ??", [
+              sortTableName + "." + childIdAttribute,
+              childTableName + "." + childIdAttribute,
+            ])
+            .as(sortOrderName)
+        )
+        .leftJoin(
+          pivotTableName,
+          pivotTableName + "." + childIdAttribute,
+          childTableName + "." + childIdAttribute
+        )
+        .where(
+          pivotTableName + "." + parentIdAttribute,
+          "=",
+          parentInstance.id
+        );
+
+      if (hasFilters) {
+        query.andWhere((builder) =>
+          queryBuilder(builder, pivotColumns, filters)
+        );
+      }
+
+      childData = await query
+        .orderBy(sortOrderName, sortDirection)
+        .offset(offset)
+        .limit(limit);
+    } else {
+      // Typical sort
+      childData = await parentInstance
+        .related(childRelation)
+        .query((builder) => {
+          if (hasFilters) {
+            builder.where((builder) =>
+              queryBuilder(builder, pivotColumns, filters)
+            );
+          }
+          builder.orderBy(sortField, sortDirection);
+          if (limit) {
+            builder.limit(limit);
+          }
+          if (offset) {
+            builder.offset(offset);
+          }
+          return builder;
+        })
+        .fetch({ withRelated });
+
+      // Fetch the pivot table in case there are any necessary values on it
+      await Promise.all(
+        childData.map(async (item) => {
+          item.set(pivotAttribute, await item.pivot.fetch());
+        })
+      );
+    }
+
+    // Count the pivot table and generate pagination
+    let rowCount;
+    if (hasFilters) {
+      rowCount = await parentInstance
+        .related(childRelation)
+        .where((builder) => queryBuilder(builder, pivotColumns, filters))
+        .count();
+    } else {
+      rowCount = await parentInstance.related(parentPivotRelation).count();
+    }
+    const pagination = buildPaginationResponse({ rowCount, query, paginate });
+
+    response.locals = { data: childData, pagination, query };
+    next();
+  };
+}
+
+/**
+ * API to fetch a single relation via the pivot table.
+ */
+export function show(service) {
+  const {
+    parent,
+    Model,
+    parentIdAttribute,
+    childIdAttribute,
+    pivotChildRelation,
+  } = getPivotModels(service);
+
+  return async function showPivotMiddleware(request, response, next) {
+    const { query, parents } = request;
+    const parentInstance = parents[parent.resource];
+    const withRelated = query.related ? query.related.split(",") : [];
+
+    let child, data;
+
+    try {
+      data = await db.show({
+        Model: Model,
+        field: childIdAttribute,
+        objectID: parseInt(request.params.id),
+        criteria: {
+          [parentIdAttribute]: parentInstance.get(parentIdAttribute),
+        },
+        withRelated,
+      });
+    } catch (error) {
+      debug(`${service.resource} Error fetching pivot`);
+      debug(error);
+      return next(error);
+    }
+
+    if (!data) {
+      response.locals = {};
+      next();
+    }
+
+    try {
+      child = await data.related(pivotChildRelation).fetch();
+    } catch (error) {
+      debug(`${service.resource} Error fetching child`);
+      debug(error);
+      return next(error);
+    }
+
+    response.locals = { child, data };
+    next();
+  };
+}
+
+/**
+ * API to insert a new record
+ */
+export function create(service) {
+  const { Model, parentIdAttribute, childIdAttribute } = getPivotModels(
+    service
+  );
+  return async function createPivotMiddleware(request, response, next) {
+    const { params } = request;
+    let data;
+    const body = reduceValidColumns(
+      request.body,
+      service.columns,
+      service.options.privateFields
+    );
+    if (service.options.parent) {
+      service.idAttributes.forEach((idAttribute) => {
+        if (idAttribute in params && idAttribute in service.columns) {
+          body[idAttribute] = parseInt(params[idAttribute]);
+        }
+      });
+    }
+    try {
+      const instances = await indexPivotTable({
+        Model,
+        parentIdAttribute,
+        parentId: body[parentIdAttribute],
+        childIdAttribute,
+        childId: body[childIdAttribute],
+      });
+      if (Array.isArray(body[childIdAttribute])) {
+        data = await handleCreateMany({
+          Model: service.Model,
+          data: body,
+          childIdAttribute,
+          instances,
+        });
+      } else {
+        data = await handleCreateOne({
+          Model: service.Model,
+          data: body,
+          instances,
+        });
+      }
+    } catch (error) {
+      debug(`${service.resource} create error`);
+      console.error(error);
+      return next(error);
+    }
+    response.locals = { data };
+    next();
+  };
+}
+
+/**
+ * API to update a single record
+ */
+export function update(service) {
+  return async function updatePivotMiddleware(request, response, next) {
+    const { data: instance } = response.locals;
+    const { user, permission } = request;
+    if (
+      permission === PERMISSIONS.ALLOW_FOR_OWNER &&
+      instance.get("user_id") !== user.user_id
+    ) {
+      return next(new Error("PermissionsError"));
+    }
+    const body = reduceValidColumns(
+      request.body,
+      service.columns,
+      service.options.privateFields
+    );
+    let data;
+    try {
+      data = await db.update({
+        instance,
+        data: body,
+      });
+    } catch (error) {
+      debug(`${service.resource} update error`);
+      debug(error);
+      next(new Error(error));
+    }
+    response.locals = { data };
+    next();
+  };
+}
+
+/**
+ * API to destroy a pivot table relation.
+ */
+export function destroy(service) {
+  const { Model, parentIdAttribute, childIdAttribute } = getPivotModels(
+    service
+  );
+  return async function destroyPivotMiddleware(request, response, next) {
+    const idsToDelete = request.params.id || request.body[childIdAttribute];
+    try {
+      const instances = await indexPivotTable({
+        Model,
+        parentIdAttribute,
+        parentId: request.params[parentIdAttribute],
+        childIdAttribute,
+        childId: idsToDelete,
+      });
+      await Promise.all(instances.map((instance) => instance.destroy()));
+    } catch (error) {
+      debug(`${service.resource} destroy error`);
+      debug(error);
+      return next(new Error(error));
+    }
+    response.locals.success = true;
+    response.locals.id = idsToDelete;
+    next();
+  };
+}
+
+/**
+ * API to destroy many records. Note that the normal destroy API accomplishes this.
+ * @type {Function}
+ */
+export const destroyMany = destroy;