CODEWITHSUNDEEP
node.jssequelize.js
Kepler
Kepler

Reputation: 129

Sequelize Association : same field with multiple table is possible?

I have the following four models

User

user_id
user_email

Permit

permit_id
permit_name

Label

label_id
label_name
module_name   (user or permit)

ModuleLabel

module_label_id  
label_id   **(fk - Label table)**
module_id  (user_id or permit_id)

How can I get mapped Label details while fetching permit data using sequelize?

Select "from" Permit, join ModuleLabel where module_id=permit_id && module_name = 'permit'(from Label table), and then join Label from there

Upvotes: 0

Views: 2927

Answers (2)

piotrbienias
piotrbienias

Reputation: 7401

It can be done but definitely without constraints in the database. In your case the module_id field in ModuleLabel may reference user or permit table. I can show you some implementation sketch, however I would suggest reconstructing the schema e.g. create UserLabel and PermitLabel models which would have foreign key constraint to User and Permit. However, below is how you can do it your way

// in User model definition
classMethods: {
    associate: function(models){
        this.hasMany(models.ModuleLabel, { foreignKey: 'module_id', constraints: false });
    }
}

// in Permit model definition
classMethods: {
    associate: function(models){
        this.hasMany(models.ModuleLabel, { foreignKey: 'module_id', constraints: false });
    }
}

Above associations allow us to join ModuleLabel model when querying both User or Permit. The most important part is to remember about the constraints: false which prevents creating database constraints. Let's move on. The Label model does not need any associations, only fields declaration. The last model is ModuleLabel

// in ModuleLabel model definition
classMethods: {
    associate: function(models){
        this.belongsTo(models.Label, { foreignKey: 'label_id' });
        this.belongsTo(models.User, { foreignKey: 'module_id', constraints: false });
        this.belongsTo(models.Permit, { foreignKey: 'module_id', constraints: false });
    }
}

Now, in order to perform your desired query, you can do this

models.Permit.findAll({
    include: [
        {
            model: models.ModuleLabel,
            include: [
                {
                    model: models.Label,
                    where: { module_name: 'permit' }
                }
            ]
        }
    ]
}).then((result) => {
    console.log(result);
});

Above query would generate SQL similar to this presented below

SELECT * FROM Permits
LEFT OUTER JOIN ModuleLabels
ON Permits.id = ModuleLabels.module_id
INNER JOIN Labels
ON ModuleLabels.label_id = Labels.id
AND Label.module_name = 'permit';

Upvotes: 2

Chad Robinson
Chad Robinson

Reputation: 4623

From your description it seems you're trying to select "from" Permit, join ModuleLabel where module_id=permit_id, and then join Label from there. If that's not correct, please clarify the question.

If that's the case, what you want to do is absolutely possible by setting up hasMany relationships. However, I advise against re-using key fields in generic ways like this (where module_id could be either a permit or user ID). I've personally spent a lot of time working with raw Entity tables in Drupal, which do the same thing - an ID could map to any number of foreign tables, and you had to evaluate each record's type to know which one to join. It made for very messy and big queries and was confusing to debug in a hurry if something got corrupted. (And easy to make mistakes.)

Upvotes: 0

Related Questions