New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

belongsToMany & foreignKey #2991

Closed
cuva opened this Issue Jan 23, 2015 · 13 comments

Comments

5 participants
@cuva

cuva commented Jan 23, 2015

Hey,

I'm using the master branch of sequelize. I'm trying to migrate my N:M model to the version 2 as I was receiving the deprecation warning.

    Vessel.belongsToMany(User, {
      through: models.UserVessel,
      foreignKey: 'vesselId'
    })

    User.belongsToMany(Vessel, {
      through: models.UserVessel,
      foreignKey: 'userId'
    })

But sequelizejs seems to create the following join table:

CREATE TABLE IF NOT EXISTS `UserVessels` (`uuid` CHAR(36) BINARY NOT NULL , `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `vesselId` CHAR(36) BINARY, `UserUuid` CHAR(36) BINARY, `userId` CHAR(36) BINARY, UNIQUE `UserVessels_userId_vesselId_unique` (`vesselId`, `userId`), UNIQUE `UserVessels_vesselId_UserUuid_unique` (`UserUuid`), PRIMARY KEY (`uuid`), FOREIGN KEY (`vesselId`) REFERENCES `Vessels` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`UserUuid`) REFERENCES `Users` (`uuid`), FOREIGN KEY (`userId`) REFERENCES `Users` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

As you can see, it created both vesselId / userId foreign keys, but it also created a UserUuid additional foreign key.

Is this a bug?
Many thanks.

@mickhansen

This comment has been minimized.

Contributor

mickhansen commented Jan 23, 2015

That sounds like a bug, although i was sure i fixed that previously:

https://github.com/sequelize/sequelize/blob/master/test/integration/associations/belongs-to-many.test.js#L814

But this doesn't test that an extra key is not generated i suppose.

@cuva

This comment has been minimized.

cuva commented Jan 24, 2015

Hi Mike,

Thanks for your answer. I've been through the tests, and it looks like the one you've referenced + the next one define these:

  var User = this.sequelize.define('User', {});
  var Place = this.sequelize.define('User', {});

instead of this I guess.

  var User = this.sequelize.define('User', {});
  var Place = this.sequelize.define('Place', {});

Not that it changes much though. Not sure just this is worth a PR.

@mickhansen

This comment has been minimized.

Contributor

mickhansen commented Jan 24, 2015

@cuva oh lol you're right, there's definitely a typo in that test.

@mickhansen mickhansen added the bug label Jan 24, 2015

@cuva

This comment has been minimized.

cuva commented Jan 26, 2015

I resolved it by setting the attribute otherKey on the options object. Not sure if that's the expected behaviour but it works fine now:

CREATE TABLE IF NOT EXISTS `UserVessels` (`uuid` CHAR(36) BINARY NOT NULL , `role` ENUM('vesseladmin', 'crewmate') NOT NULL DEFAULT 'crewmate', `enabled` TINYINT(1) DEFAULT true, `push` TINYINT(1) DEFAULT false, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `vesselId` CHAR(36) BINARY, `userId` CHAR(36) BINARY, UNIQUE `UserVessels_userId_vesselId_unique` (`vesselId`, `userId`), PRIMARY KEY (`uuid`), FOREIGN KEY (`vesselId`) REFERENCES `Vessels` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`userId`) REFERENCES `Users` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

I'll let you close the issue if there's no need for intervention. :)

@mickhansen

This comment has been minimized.

Contributor

mickhansen commented Jan 27, 2015

It should work without otherKey too so keeping it open for now.

@mickhansen

This comment has been minimized.

Contributor

mickhansen commented Feb 17, 2015

Fixed in sequelize@2.0.3

@jose-nunez

This comment has been minimized.

jose-nunez commented Oct 27, 2015

Hey guys. I'm having the same problem here (sequelize@3.12.2)
This is my association table:

sequelize.define('category_product',{
    category_id: {
      type: DataTypes.INTEGER,
      allowNull: false
    },
    product_id: {
      type: DataTypes.INTEGER,
      allowNull: false
    }
  });

The relations I'm making:

db.product.belongsToMany(db.category,{through:db.category_product,foreignKey:'product_id'});
db.category.belongsToMany(db.product,{through:db.category_product,foreignKey:'category_id'});

And this is the output I'm getting:

CREATE TABLE category_product (
    category_id INTEGER  NOT NULL
                         REFERENCES category (id) ON DELETE CASCADE
                                                  ON UPDATE CASCADE,
    product_id  INTEGER  PRIMARY KEY,
    created     DATETIME NOT NULL,
    updated     DATETIME NOT NULL,
    UNIQUE (
        category_id,
        product_id
    )
);

Note that foreign key names in category_product table should stay named like that, since that model is autogenerated from a legacy DB.
¿Am I doing something wrong here?

Thank you!

EDITED:

product_id is not referencing product table, and also both foreign keys should be a combined primary key (not that important). I was expecting something like this:

CREATE TABLE category_product (
    category_id INTEGER  PRIMARY KEY NOT NULL
                         REFERENCES category (id) ON DELETE CASCADE
                                                  ON UPDATE CASCADE,
    product_id  INTEGER  PRIMARY KEY NOT NULL
                         REFERENCES product (id) ON DELETE CASCADE
                                                  ON UPDATE CASCADE,
    created     DATETIME NOT NULL,
    updated     DATETIME NOT NULL,
    UNIQUE (
        category_id,
        product_id
    )
);
@janmeier

This comment has been minimized.

Member

janmeier commented Oct 27, 2015

@peponerock I'm not sure what the issue is? product id being marked a primary key?

@jose-nunez

This comment has been minimized.

jose-nunez commented Oct 27, 2015

Please, read the comment above. Thanks

@janmeier

This comment has been minimized.

Member

janmeier commented Oct 27, 2015

I've read it, please give me time to respond ;)

var CategoryProduct = sequelize.define('category_product',{
  category_id: {
    type: DataTypes.INTEGER,
    allowNull: false,
    primaryKey: true
  },
  product_id: {
    type: DataTypes.INTEGER,
    allowNull: false,
    primaryKey: true
  }
});
CREATE TABLE IF NOT EXISTS `category_products` (`category_id` INTEGER NOT NULL NOT NULL REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, `product_id` INTEGER NOT NULL NOT NULL REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE (category_id, product_id), PRIMARY KEY (`category_id`, `product_id`));
@jose-nunez

This comment has been minimized.

jose-nunez commented Oct 27, 2015

Thanks a lot! (I'm still learning btw)

@tyler-canton

This comment has been minimized.

tyler-canton commented Feb 24, 2017

@jose-nunez How are you getting the output. I'm trying to migrate two models with 'belongsToMany' relationship, this work, however, I'm not seeing the through table within the database. Or does this create the model in sequelize?

@jose-nunez

This comment has been minimized.

jose-nunez commented Feb 25, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment