Methods to simplify query writing while joining tables

[vellotis]

I am a fan of this library. :+1: And just may have a neat suggestion for additional functionality.

I am using some neat methods that have easened where and join queries.

Add to Model static methods...

// Helper
  var formatFieldName = function( fieldName ) {
    if ( ( fieldName != null ? fieldName.trim() : undefined ) !== '*' ) {
      fieldName = "`" + fieldName + "`";
    }
    return fieldName;
  };

// Static methods

  field: function( fieldName ) {
    return bookshelf.knex.raw( "`" + this.prototype.tableName + "`." + formatFieldName( fieldName ) );
  },

  /**
   * Method to 
   */
  alias: function(alias) {
    var result;
    // Raw table alias declaration
    result = Models.Bookshelf.knex.raw("`" + this.prototype.tableName + "` `" + alias + "`");
    // Add "field" method to declared alias
    result.field = function(fieldName) {
      return bookshelf.knex.raw("`" + alias + "`." + (formatFieldName(fieldName)));
    };
    return result;
  }

Example of usage (in CoffeeScript):

  Company.forge().query (_) ->
    _.where Company.field('reg_no'), req.params.reg_no
  .fetch
    require: true
  .then (company) ->

      company
      .transactions()
      .query (_) ->
          employee_person = Person.alias('employee')
          terminals_alias = Terminal.alias('terminals_alias')
          _.whereNotNull(Person.field('personal_id'))
          # Joining
          _.leftOuterJoin Client::tableName, Transaction.field('client_id'), Client.field('id')
          _.leftOuterJoin Person::tableName, Client.field('person_id'), Person.field('id')
          _.leftOuterJoin EmployeeTerminal::tableName, Transaction.field('employee_terminal_id'), EmployeeTerminal.field('id')
          _.leftOuterJoin CompanyEmployee::tableName, EmployeeTerminal.field('company_employee_id'), CompanyEmployee.field('id')
          _.leftOuterJoin employee_person, CompanyEmployee.field('person_id'), employee_person.field('id')
          _.leftOuterJoin terminals_alias, Transaction.field('terminal_id'), terminals_alias.field('id')
          _.leftOuterJoin Company::tableName, terminals_alias.field('company_id'), Company.field('id')
          # ngAdmin pagination
          if req.query._perPage?
            _.limit(req.query._perPage)
            _.offset((req.query._page - 1) * req.query._perPage) if req.query._page?
          # ngAdmin sorting
          _.orderBy(req.query._sortField, req.query._sortDir || 'DESC') if req.query._sortField?
          # Returns
          _.select(
            Transaction.field('*')
            Bookshelf.knex.raw("#{ terminals_alias.field('name') } as terminal_name")
            Bookshelf.knex.raw("#{ Person.field('personal_id') } as client_personal_id")
            Bookshelf.knex.raw("#{ Person.field('first_name') } as client_first_name")
            Bookshelf.knex.raw("#{ Person.field('last_name') } as client_last_name")
            Bookshelf.knex.raw("#{ Company.field('reg_no') } as company_reg_no")
            Bookshelf.knex.raw("#{ employee_person.field('personal_id') } as employee_personal_id")
            Bookshelf.knex.raw("#{ employee_person.field('first_name') } as employee_first_name")
            Bookshelf.knex.raw("#{ employee_person.field('last_name') } as employee_last_name")
            )
      .fetch()

In case this is not a case of discussion be free to close the issue. I would contribute for PR.

[bsiddiqui]

@vellotis this looks interesting - sorry for the delay, will take a look tomorrow

[vellotis]

@bsiddiqui There is one thing that bookshelf-fields plugin uses the same method name 'field'. Maybe it would be better to name them different. Or event make them accessible through a property?

  function QueryHelper(model) {
    this.context = model;
  }
  QueryHelper.prototype.field = function () {
    return bookshelf.knex.raw(
      "`" + this.context.prototype.tableName + "`." + formatFieldName( fieldName )
    );
  };
  // and alias declaration

  var SomeModel = Model.extend({
    qh: function () {
      return new QueryHelper(this); 
    }
  });

  SomeMode.query(function (query) {
    var qh = this.qh();
    query.where(this.qh().field('field_name', '=', true);
  }).fetch();
  // etc