How to Include relationship on model via enum fields in sequelize-CodePudding

I have a table, table1, with a status field with type enum of values ['active', 'inactive']. I have another table, table2, with a status field with type enum of values ['active', 'inactive'].

I tried adding a relationship between them as such:

table1.hasMany(models.table2, {
                foreignKey: 'status', // enum field
                sourceKey: 'status' //  enum field
            })

when I try to query the field including the relationship as such:

let result = await models.model1.findAll({
include: [{
model: models.model2
}]
})

I get the following error message.

"operator does not exist: \"enum_table1_status\" = \"enum_table2_status\""

What I want is for each element in result, there'd be an associated table property table2 which would be an array of the rows in table2 which have the same status with the status of the parent object from table1

I know it could probably work if both tables had the same enum type assigned to them, but I don't know how to do that.

CodePudding user response：

I figured out how to go about it. When creating relationships in sequelize, the types have to be the same. string - string, integer to integer, etc. enums are a bit trickier apparently. Even if 2 enums have the same values, they are of different types like in the question above. So, to create a relationship, the the columns being related should have the same enum type.

Now, sequelize, to the best of my knowledge, doesn't have the functionality of creating enum types independent of a table/model. But, it is possible to assign type to an already created enum type. in PGadmin, the created types can be found under types, under schemas, under the db. Like this:

To assign the enum type of an already created table to another simply get the enum type name from the list of types, and assign it to the type variable when creating the migration file and model. Like this:

'use strict';
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('table2', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      loan_type: {
          // Re-using the same ENUM than `status` so that we can create a relationship between them.
          type: '"public"."enum_table1_status"',
          allowNull: false,
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('table2');
  }
};

With this, relationships can be created on the model level between both tables.