I create an api in express.js, node.js, mysql and sequelize.

I have two models : Custom fields and Customer Types

Here is my customer type model :

module.exports = (sequelize, DataTypes) => {

    const CustomerType = sequelize.define("customerType", {
        name: {
            type: DataTypes.STRING,
            allowNull: false
        },
        description: {
            type: DataTypes.STRING,
            allowNull: false
        },
        user_id: {
            type: DataTypes.STRING,
            allowNull: false
        }
    })

    return CustomerType
}

And there is my custom fields model :

const customerType = require('./customerTypeModel')

module.exports = (sequelize, DataTypes) => {

    const CustomField = sequelize.define("customField", {
        customer_type_ids: [
            {
                type: DataTypes.INTEGER,
                references: {
                    model: customerType,
                    key: "id"
                }
            }
        ],
        user_id: {
            type: DataTypes.STRING,
            allowNull: false
        },
        system_name: {
            type: DataTypes.STRING,
            allowNull: true
        },
        title: {
            type: DataTypes.STRING,
            allowNull: true
        },
        type: {
            type: DataTypes.STRING,
            allowNull: true
        },
        required: {
            type: DataTypes.BOOLEAN,
            allowNull: true
        },
    })
    return CustomField
}

I need to have in my customField table an array of id from the customerType table. For exemple i need to get a json like this :

{
 "id": 1
 "customer_types_ids": [ 
   {
     "id": 1,
     "name": "Customers",
     "description": "Customers",
     "user_id": "123456"
   },
   {
     "id": 2,
     "name": "Leads",
     "description": "Leads",
     "user_id": "123456"
   }
],
 "user_id": "123456",
 "system_name": "firstname",
 "title": "Firstname",
 "type": "text",
 "required": true
}

How should I structure my custom field Model to be able to have a json like this in return?

Thanks for your help !

CodePudding user response：

I assume you're expecting a One-to-Many relationship between CustomField and CustomerType. In this case, CustomerType owns the relation, so there is a reference to CustomField in the relational table of CustomerType called customFieldId.

// customerType.js
module.exports = (sequelize, DataTypes) => {
    const CustomerType = sequelize.define("customerType", {
        name: {
            type: DataTypes.STRING,
            allowNull: false
        },
        description: {
            type: DataTypes.STRING,
            allowNull: false
        },
        user_id: {
            type: DataTypes.STRING,
            allowNull: false
        }
    });

    return CustomerType;
}

// customField.js
module.exports = (sequelize, DataTypes) => {
    const CustomField = sequelize.define("customField", {
        user_id: {
            type: DataTypes.STRING,
            allowNull: false
        },
        system_name: {
            type: DataTypes.STRING,
            allowNull: true
        },
        title: {
            type: DataTypes.STRING,
            allowNull: true
        },
        type: {
            type: DataTypes.STRING,
            allowNull: true
        },
        required: {
            type: DataTypes.BOOLEAN,
            allowNull: true
        },
    });

    return CustomField;
}

// db.js
...
const CustomerType = require("./customerType.js")(sequelize, Sequelize.DataTypes);
const CustomField = require("./customField.js")(sequelize, Sequelize.DataTypes);

CustomField.hasMany(CustomerType, { as: "customer_types_ids" });
CustomerType.belongsTo(CustomField, {
    foreignKey: "id",
    as: "customFieldId",
});

This should define two relational tables with a One-to-Many relationship. The following code can be used to load a CustomField with all its CustomerTypes.

CustomField.findByPk(id, { include: ["customer_types_ids"] });