Question

26

Sequelize hasMany through another table

rated 0 times [ 32] [ 6] / answers: 1 / hits: 16152 / 9 Years ago, fri, march 20, 2015, 12:00:00

Okay 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 relation between the tables are an 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);

            }

it returns nothing. But if i delete the relationship it returns only the Module

Can anyone tell me what i am doing wrong?

When i debug

When i debug it does not log any sql sadly it seems it is just ignoring the sql call ?

Answers

Only authorized users can answer the question. Please sign in first, or register a free account.

mckinley

Add To Favorites

Follow

Total Points: 15

Total Questions: 101

Total Answers: 94

Location: Liechtenstein

Member since Fri, Sep 11, 2020

4 Years ago

answered 9 Years ago tayaw · Accepted Answer

From my understanding, the tables you've created a many to many relationship and that when you query for a Module, you want all the Competence associated to it.

Though I wasn't able to get .hasMany() working, the error produced was:

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 which this output is almost impossible to debug.

However, I was able to create your model bindings and query it correctly with the .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'

})

Which outputs a query that you're 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;

To my understanding .hasMany references a 1:M relationship, which does not require a join table.

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

Here's the gist of how I got to this conclusion:

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