Sequelize.js - How to create non-trivial associations without raw SQL?

Question

Here is my situation:

I'm using postgres 9.4, Sequelize ORM and have following models:

• Service

  • serviceCode - primary key, string of 6 characters
  • serviceTitle - string

• ServiceGroup

  • serviceCodePrefixes - array of strings that are prefixes for Service.serviceCode
  • serviceGroupTitle - string

• Task

  • serviceCode - reference to Service

I need to build Task object populated with Service and ServiceGroup objects. Example:

In database:
Service {
  serviceCode: '123232',
  serviceTitle: 'svc title #1',
}

ServiceGroup {
  serviceCodePrefix: ['12', '13', '92', ...],
  serviceGroupTitle: 'svc grp title #1',
}

Task {
  serviceCode: '123232',
}

Result:
Task {
  service: {
    serviceTitle: 'svc title #1',
  },
  serviceGroup: {
    serviceGroupTitle: 'svc grp title #1',
  },
}

The problem is that serviceCodePrefix contains not simple IDs, which can be used to create association using hasOne/belongsTo/etc., but prefix for ID.

So questions is: how this can be done without raw sql?

Answer 1

Turns out that right now Sequelize has experimental feature: 'on' option for 'include'. This option allows users to customize joining conditions. So my problem can be solved this way:

const Service = sequelize.define('service', {
  serviceTitle: Sequelize.STRING,
  serviceCode: Sequelize.STRING,
});

const ServiceGroup = sequelize.define('service_group', {
  serviceGroupTitle: Sequelize.STRING,
  // Array of prefixes (e.g. ['01%', '023%'])
  serviceCodePrefix: Sequelize.ARRAY(Sequelize.STRING),
});

const Task = sequelize.define('task', {
  taskTitle: Sequelize.STRING,
  serviceCode: Sequelize.STRING,
});

Task.belongsTo(Service, { foreignKey: 'serviceCode' });

// Hack needed to allow 'include' option to work
Task.hasMany(ServiceGroup, { foreignKey: 'serviceCodePrefix', constraints: false });

// And finally
Task.findAll({
  include: [
    { model: Service },
    {
      model: ServiceGroup,
      on: [' "task"."serviceCode" LIKE ANY("serviceGroup"."serviceCodePrefix") '],
    },
  ],
});

Not sure about the performance though.