kanso-orm/lib/query.js

'use strict';

var algorithms = {

  /**
   * where
   * @param {Object} query
   * @param {string} table
   * @param {Object} where
   * @param {int} count
   */
  where: function(query, table, where, count) {
    count = (typeof count === 'number') ? count : 0;

    // Decide between AND or OR
    let decideWhereAndOr = (table, column, operator, value, pref) => {
      if(!count) {
        query = query.where([table, column], operator, value);
      }
      else {
        if(pref && pref === 'or') {
          query = query.or([table, column], operator, value);
        }
        else if (pref && pref === 'and') {
          query = query.and([table, column], operator, value);
        }
      }
      count++;
    };

    for(let column in where) {
      if(typeof where[column] === 'object') {
        for(let op in where[column]) {
          let operator = algorithms.getOperator(op);
          if(Array.isArray(where[column][op])) {
            where[column][op].map((value) => {
              let pref = (op === '$and') ? 'and' : 'or'
              decideWhereAndOr(table, column, '=', value, pref);
            });
          }
          else {
            decideWhereAndOr(table, column, operator, where[column][op], 'and');
          }
        }
      } else {
        decideWhereAndOr(table, column, '=', where[column], 'and');
      }
    }

    return {
      query: query,
      count: count
    };
  },

  /**
   * getOperator
   * @param {string} operator
   */
  getOperator: function(operator) {
    let result;
    switch(operator) {
      case '$eq':
        result = '=';
        break;
      case '$ne':
        result = '!=';
        break;
      case '$lt':
        result = '<';
        break;
      case '$gt':
        result = '>';
        break;
      case '$lte':
        result = '<=';
        break;
      case '$gte':
        result = '>=';
        break;
      case '$like':
        result = 'LIKE';
        break;
      case '$notLike':
        result = 'NOT LIKE';
        break;
      case '$or':
        result = 'or';
        break;
      case '$and':
        result = 'and';
        break;
    }
    return result;
  }

};

var Query = {

  /**
   * Create
   * @param {Object} params
   * @param {Object} params.ORM
   * @param {string} params.table
   * @param {Object} params.data
   * @param {string[]} params.returning
   */
  create: function(params) {
    return params.ORM.querier.table(params.table)
    .insert([params.data])
    .returning(params.returning)
    .exec();
  },

  /**
   * Update
   * @param {Object} params
   * @param {Object} params.ORM
   * @param {string} params.table
   * @param {Object} params.where
   * @param {Object} params.data
   * @param {string[]} params.returning
   */
  update: function(params) {
    let query = params.ORM.querier.table(params.table);
    query = query.update(params.data);
    let whereCount = 0;
    let results = algorithms.where(query, params.table, params.where, whereCount);
    whereCount += results.count;
    query = results.query;
    query = query.returning(params.returning);
    return query.exec();
  },

  /**
   * Find
   * @param {Object} params
   * @param {Object} params.ORM
   * @param {string} params.table
   * @param {Object} params.join
   * @param {Object} params.where
   * @param {Object} params.relationships
   * @param {Object} params.options
   * @param {string[]} params.returning
   */
  find: function(params) {
    return new Promise((resolve, reject) => {
      //
      // Query
      let query = params.ORM.querier.table(params.table)
      .distinct()
      .select([[params.table, '*']]);
      //
      // Join
      for(let table in params.join) {
        let relationship = params.relationships[table];
        let throughTable = relationship.getThroughTable();
        let identiferA, identiferB;
        if(relationship.type === 'BELONGS_TO_ONE') {
          identiferA = relationship.identifer.parent;
          identiferB = relationship.identifer.child;
        } else {
          identiferA = relationship.identifer.child;
          identiferB = relationship.identifer.parent;
        }
        query.innerJoin(throughTable).on(
          [throughTable, identiferA], '=', [params.table, identiferB]);
      }

      // Where
      let whereCount = 0;

      // Where table attributes
      let results = algorithms.where(query, params.table, params.where, whereCount);
      whereCount += results.count;
      query = results.query;

      // Where join attributes
      for(let table in params.join) {
        let relationship = params.relationships[table];
        let throughTable = relationship.getThroughTable();
        let relationTable = relationship.modelFactoryName;

        //
        // BelongsToMany
        if(relationship.type === 'BELONGS_TO_MANY') {
          let targetModelFactory = relationship.getTargetModel();
          // Relation select
          let relation = params.ORM.querier.table(throughTable)
          .select([[throughTable, relationship.identifer.child]])
          .innerJoin(relationTable)
          .on(
            [throughTable, targetModelFactory.identifer.child],
            '=',
            [relationTable, targetModelFactory.identifer.parent]
          );
          // Relation where
          let joinWhereCount = 0;
          let results = algorithms.where(relation, relationTable, params.join[table], joinWhereCount);
          joinWhereCount += results.count;
          relation = results.query;
          // In
          if(!whereCount) {
            query = query.where([params.table, relationship.identifer.parent]);
          } else {
            query = query.and([params.table, relationship.identifer.parent]);
          }
          query = query.in(relation.sql());
          whereCount++;
        }

        //
        // BelongsToOne
        // HasOne
        // HasMany
        else {
          // Where table attributes
          let results = algorithms.where(query, relationTable, params.join[table], whereCount);
          whereCount += results.count;
          query = results.query;
        }

      }
      //
      // Execute
      query.exec()
      .then((results) => resolve(results))
      .catch((error) => reject(error));
    });
  },

  /**
   * Delete
   * @param {Object} params
   * @param {Object} params.ORM
   * @param {string} params.table
   * @param {Object} params.where
   * @param {string[]} params.returning
   */
  delete: function(params) {
    let query = params.ORM.querier.table(params.table);
    query = query.delete();
    let whereCount = 0;
    let results = algorithms.where(query, params.table, params.where, whereCount);
    whereCount += results.count;
    query = results.query;
    query = query.returning(params.returning);
    return query.exec();
  }

};

module.exports = Query;