Objection.js: Please help with custom columnNameMappers

[jejrthompson]

I have this mapper signature and it is not working with filters specified...

static columnNameMappers = {
    parse(obj) {
      // database --> code
      return {
        id: obj.MktTypeID,
        desc: obj.MktTypeDesc,
        entryDesc: obj.MktTypeEntryDesc,
        active: obj.Active
      };
    },

    format(obj) {
      // code --> database
      return {
        MktTypeID: obj.id,
        MktTypeDesc: obj.desc,
        MktTypeEntryDesc: obj.entryDesc,
        Active: obj.active
      };
    }
  };

Any help greatly appreciated!

[koskimas]

To be able to help you, I need you to be a little more specific.

1. How is it not working?
2. What is happening?
3. What do you expect to happen?
4. What do you mean by filters (where clauses, named filters, modifyEager, relation filters, ...) and what kind of filters are you trying to use?
5. What's the rest of the model like?
6. What version of objection are you using?

[koskimas]

@jejrthompson Did you solve this already? Can I close this issue?

[koskimas]

Ok, I'll try to quess what you meant:

1. You are getting undefined values when you use select for you queries? That's because you always map all columns even though you only select a subset. You need to test the input object separately for each column.

2. You are trying to make the mapping work also in query builder methods like where. For example where('id', 1) instead of where('MktTypeID', 1)? That kind of mapping needs to be done on the knex level. Knex has the wrapIdentifier and postProcessResponse functions that can be used, but those are really low-level functions and you need to handle a million corner cases. Objection has a knexIdentifierMapping helper (not public yet, but will be soon. You can already use it though with 1.0 RC7) that makes the mapping alot easier.

const { knexIdentifierMapping } = require('objection');

const knex = require('knex')({
  client: 'postgres',
  connection,

  // You need to list all identifiers here (tables, columns, aliases, everything you want to map).
  ...knexIdentifierMapping({
    MktTypeID: 'id',
    MktTypeDesc: 'desc',
    MktTypeEntryDesc: 'entryDesc',
    Active: 'active'
  })
});

[jejrthompson]

Sorry, I was out of town this weekend.

I'm actually using objection-find to parse my URL queries into objection queries and it's throwing this error. I thought it may be an objection error because the function propertyNameToColumnName isn't returning a column mapping. Idk.

EDIT: I'm passing in a query such: /api/v1/MktType?id=86

Error: PropertyRef: unknown property id
application.js:630
    at Object.throwError (c:\git\DCST\api\node_modules\objection-find\lib\utils.js:3:19)
    at PropertyRef._parse (c:\git\DCST\api\node_modules\objection-find\lib\PropertyRef.js:95:13)
    at new PropertyRef (c:\git\DCST\api\node_modules\objection-find\lib\PropertyRef.js:68:10)
    at FindQueryBuilder._parsePropertyRef (c:\git\DCST\api\node_modules\objection-find\lib\FindQueryBuilder.js:498:37)
    at _.reduce (c:\git\DCST\api\node_modules\objection-find\lib\FindQueryBuilder.js:489:28)
    at arrayReduce (c:\git\DCST\api\node_modules\lodash\lodash.js:704:21)
    at Function.reduce (c:\git\DCST\api\node_modules\lodash\lodash.js:9698:14)
    at FindQueryBuilder._parsePropertyRefs (c:\git\DCST\api\node_modules\objection-find\lib\FindQueryBuilder.js:486:14)
    at QueryParameter._parseFilter (c:\git\DCST\api\node_modules\objection-find\lib\QueryParameter.js:89:33)
    at QueryParameter._parse (c:\git\DCST\api\node_modules\objection-find\lib\QueryParameter.js:58:12)
    at new QueryParameter (c:\git\DCST\api\node_modules\objection-find\lib\QueryParameter.js:51:10)
    at _.each (c:\git\DCST\api\node_modules\objection-find\lib\FindQueryBuilder.js:352:21)
    at c:\git\DCST\api\node_modules\lodash\lodash.js:4944:15

I'm thinking I need to do more digging into how objection-find builds the queries and maybe look for an alternate solution. I'm basically writing a new API on top of a legacy db. We will eventually create a new db in another phase, but for now I wanted to use more friendly column names so the db swap out would be easier.

[jejrthompson]

I would expect a call to the function propertyNameToColumnName('id') to return 'MktTypeID' given the columnNameMappers config in my OP.

I can use the knexIdentifierMapping but this could get very messy over 100+ db tables. Is this the only solution?

[koskimas]

@jejrthompson knexIdentifierMapping is the only way. You need to define those conversions somewhere right? How could that possibly be automatic? So what's the problem? You can easily define them in your models and do something like this:

const { knexIdentifierMapping } = require('objection');
const Knex = require('knex');
const path = require('path')
const fs = require('fs');

// Path to your model folder.
const MODELS_PATH = path.join(__dirname, 'models');

const knex = Knex({
  client: 'postgres',

  connection: {
    host: '127.0.0.1',
    user: 'objection',
    database: 'objection_test'
  }

  // Go through all models and add conversions using the custom property
  // `column` in json schema. If you don't use jsonSchema you can add any
  // static property that holds the mappings to your model.
  ...knexIdentifierMapping(fs.readdirSync(MODELS_PATH)
    .filter(it => it.endsWith('.js'))
    .map(it => require(path.join(MODELS_PATH, it)))
    .reduce((mapping, modelClass) => {
      const properties = (modelClass.jsonSchema || {}).properties || {};
      return Object.keys(properties).reduce((mapping, propName) => {
        mapping[properties[propName].column] = propName;
        return mapping;
      }, mapping);
    }, {});
  )
});

[koskimas]

columnNameMappers could work for you partially (you would still need to use the column names in customn queries), but you need to implement them so that you check the existence of each property:

function createColumnMappers(columnToProp) {
  const propToColumn = Object.keys(columnToProp).reduce((propToColumn, column) => {
    propToColumn[columnToProp[column]] = column;
    return reverse;
  }, {});

  return {
    parse(obj) {
      return Object.keys(obj).reduce((mapped, column) => {
        mapped[columnToProp[column] || column] = obj[column];
        return mapped;
      }, {});
    },

    format(obj) {
      return Object.keys(obj).reduce((mapped, prop) => {
        mapped[propToColumn[prop] || prop] = obj[prop];
        return mapped;
      }, {});
    }
  };
}

// In your models

static columnNameMappers = createColumnMappers({
  MktTypeID: 'id',
  MktTypeDesc: 'desc',
  MktTypeEntryDesc: 'entryDesc',
  Active: 'active'
})

[koskimas]

You could even map the names automatically if they always follow the same format like <tableName>PropName --> propName

const { lodash } = require('objection');

function createColumnMappers(tableName) {
  return {
    parse(obj) {
      return Object.keys(obj).reduce((mapped, column) => {
        const prop = lodash.lowerFirst(column.substr(tableName.length));
        mapped[prop] = obj[column];
        return mapped;
      }, {});
    },

    format(obj) {
      return Object.keys(obj).reduce((mapped, prop) => {
        const column = tableName + lodash.upperFirst(prop);
        mapped[column] = obj[prop];
        return mapped;
      }, {});
    }
  };
}