All geek questions in one place

Sequelize hasMany through another table

So, I have the following three models

Module:

  var Module = sequelize.define('module', { id: DataTypes.INTEGER, name: DataTypes.STRING, description: DataTypes.STRING, category_id: DataTypes.STRING, module_type_id: DataTypes.STRING, gives_score: DataTypes.INTEGER, duration: DataTypes.STRING, price: DataTypes.STRING }, { freezeTableName: true} )

Competence:

 Competence = sequelize.define('competence', { id: DataTypes.INTEGER, name: DataTypes.STRING, organization_id: DataTypes.INTEGER, competence_type_id: DataTypes.INTEGER },{freezeTableName:true})

Module_has_competence:

  Module_has_competence = sequelize.define('module_has_competence', { id: DataTypes.INTEGER, module_id: DataTypes.INTEGER, competence_id: DataTypes.INTEGER, score: DataTypes.STRING },{ freezeTableName: true} })

As you can see, the ratio between the tables: n:m

So now I want to find all the Competence that a Module has:

So, I created the following relationship:

 Module.hasMany(Competence, {through: Module_has_competence, foreignKey: 'module_id'});

However, when I try to run:

  retrieveById: function (quote_id, onSuccess, onError) { Module.find({include: [{ all: true }],where: {id: quote_id}}) .success(onSuccess).error(onError); }

he returns nothing. But if I remove the link, it only returns Module

Can someone tell me what I am doing wrong?

When i debug

When I debug it does not register any sql, unfortunately it looks like it just ignores the sql call?

+5
source share
1 answer

From my point of view, the tables in which you have created many different relationships, and that when you request a "Module", you want all the "Competence" associated with it to be associated with it.

Although I was unable to get .hasMany() , an error occurred:

 Trace: [TypeError: Cannot call method 'replace' of undefined] at null.<anonymous> (/Users/sjlu/Development/29165644/app.js:61:13) at tryCatch1 (/Users/sjlu/Development/29165644/node_modules/sequelize/node_modules/bluebird/js/main/util.js:45:21) at Promise._callHandler (/Users/sjlu/Development/29165644/node_modules/sequelize/node_modules/bluebird/js/main/promise.js:571:13) at Promise._settlePromiseFromHandler (/Users/sjlu/Development/29165644/node_modules/sequelize/node_modules/bluebird/js/main/promise.js:581:18) at Promise._settlePromiseAt (/Users/sjlu/Development/29165644/node_modules/sequelize/node_modules/bluebird/js/main/promise.js:713:18) at Promise._settlePromiseAt (/Users/sjlu/Development/29165644/node_modules/sequelize/lib/promise.js:76:18) at Promise._settlePromises (/Users/sjlu/Development/29165644/node_modules/sequelize/node_modules/bluebird/js/main/promise.js:854:14) at Async._consumeFunctionBuffer (/Users/sjlu/Development/29165644/node_modules/sequelize/node_modules/bluebird/js/main/async.js:85:12) at Async.consumeFunctionBuffer (/Users/sjlu/Development/29165644/node_modules/sequelize/node_modules/bluebird/js/main/async.js:40:14) at process._tickCallback (node.js:442:13)

In this case, this conclusion is almost impossible to debug.

However, I managed to create bindings to your model and correctly request it using .belongsToMany() associations

 Competence.belongsToMany(Module, { through: Module_has_competence, as: 'module', foreignKey: 'module_id' }) Module.belongsToMany(Competence, { through: Module_has_competence, as: 'competence', foreignKey: 'competence_id' })

Displays the query you are looking for

  SELECT `module`.`id`, `module`.`name`, `module`.`description`, `module`.`category_id`, `module`.`module_type_id`, `module`.`gives_score`, `module`.`duration`, `module`.`price`, `module`.`createdat`, `module`.`updatedat`, `competence`.`id` AS `competence.id`, `competence`.`name` AS `competence.name`, `competence`.`organization_id` AS `competence.organization_id`, `competence`.`competence_type_id` AS `competence.competence_type_id`, `competence`.`createdat` AS `competence.createdAt`, `competence`.`updatedat` AS `competence.updatedAt`, `competence.module_has_competence`.`id` AS `competence.module_has_competence.id`, `competence.module_has_competence`.`module_id` AS `competence.module_has_competence.module_id`, `competence.module_has_competence`.`competence_id` AS `competence.module_has_competence.competence_id`, `competence.module_has_competence`.`score` AS `competence.module_has_competence.score`, `competence.module_has_competence`.`createdat` AS `competence.module_has_competence.createdAt`, `competence.module_has_competence`.`updatedat` AS `competence.module_has_competence.updatedAt` FROM `module` AS `module` LEFT OUTER JOIN (`module_has_competence` AS `competence.module_has_competence` INNER JOIN `competence` AS `competence` ON `competence`.`id` = `competence.module_has_competence`.`module_id`) ON `module`.`id` = `competence.module_has_competence`.`competence_id` WHERE `module`.`id` = 1;

In my understanding .hasMany refers to a 1: M relationship that does not require a join table.

http://docs.sequelizejs.com/en/latest/docs/associations/#one-to-many-associations

Here is the gist of how I got to this:

https://gist.github.com/sjlu/fa5c2e0e267cd692418a

+4
source

Source: https://habr.com/ru/post/1215744/

More articles:


All Articles