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

here situation:

i'm using postgres 9.4, sequelize orm , have following models:

• service

  • servicecode - primary key, string of 6 characters
  • servicetitle - string

• servicegroup

  • servicecodeprefixes - array of strings prefixes service.servicecode
  • servicegrouptitle - string

• task

  • servicecode - reference service

i need build task object populated service , 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 servicecodeprefix contains not simple ids, can used create association using hasone/belongsto/etc., prefix id.

so questions is: how can done without raw sql?

turns out right sequelize has experimental feature: 'on' option 'include'. option allows users customize joining conditions. problem can solved 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 allow 'include' option work task.hasmany(servicegroup, { foreignkey: 'servicecodeprefix', constraints: false });  // , task.findall({   include: [     { model: service },     {       model: servicegroup,       on: [' "task"."servicecode" any("servicegroup"."servicecodeprefix") '],     },   ], }); 

not sure performance though.