如何在Sequelize中查询多对多关系数据

问题描述

我在两个模型、用户和组之间建立了多对多关系.

I have got a many-to-many relationship between two models, users and groups.

我有两个模型，它们使用外键和通过属性指定belongsToMany.

I have two models which are specifying the belongsToMany with a foreignKey and through attribute.

用户模型

'use strict';
module.exports = function(sequelize, DataTypes) {
  var User = sequelize.define('User', {
    firstName: DataTypes.STRING,
    lastName: DataTypes.STRING,
    email: DataTypes.STRING,
    username: DataTypes.STRING,
    facebookId: DataTypes.STRING,
    password: DataTypes.STRING,
  }, {
    classMethods: {
      associate: function(models) {
        User.hasMany(models.Payment);
        User.hasMany(models.Friend, {foreignKey: 'userIdLink1'});
        User.belongsToMany(models.Group, { through: 'UsersGroups', foreignKey: 'facebookId' });
      }
    },
    instanceMethods: {
      toJSON: function () {
        var values = Object.assign({}, this.get());

        delete values.password;
        return values;
      }
    }
  });
  return User;
};

分组模型

'use strict';
module.exports = function(sequelize, DataTypes) {
  var Group = sequelize.define('Group', {
    name: DataTypes.STRING
  }, {
    classMethods: {
      associate: function(models) {
        // associations can be defined here
        Group.belongsToMany(models.User, {through: 'UsersGroups', foreignKey: 'groupId'});
        Group.hasMany(models.Payment)
      }
    },
    instanceMethods: {
      toJSON: function () {
        var values = Object.assign({}, this.get());

        delete values.password;
        return values;
      }
    }
  });
  return Group;
};

通过联结表加入UsersGroups

这很好用，我可以创建一个新组并将用户与其成功链接，但是当我尝试获取数据时，SQL 查询正在尝试根据 User.id 查找组，而不是像我指定的 User.facebookId在我的模型中 User.belongsToMany(models.Group, { through: 'UsersGroups', foreignKey: 'facebookId' });

This works fine and I can create a new group and it links the user with it successfully but when I try fetch the data the SQL query is trying to find Groups based on User.id as opposed to User.facebookId like I specified in my model User.belongsToMany(models.Group, { through: 'UsersGroups', foreignKey: 'facebookId' });

我调用以下代码来获取数据:

I call the following code to fetch the data:

const options = {
        where: {
                    facebookId: facebookId,

      },
      defaults: {
            firstName: data.firstName,
            lastName: data.lastName,
            email: data.email,
            facebookId: facebookId
        },
        include: [
            { model: db.Group }
        ]
    }

    db.User.findOrCreate(options)
        .then((user) => {
            res.send(user)
        }, (err) => {
            res.status(500).send(err)
        })

它返回一个用户，但有一个空的 Groups 数组，这是不正确的，因为那里肯定有数据，因为我可以很好地创建它并在数据库中看到它.

and it returns a user but with an empty Groups array which is incorrect as there is definitely data there as I can create it fine and see it in the DB.

您可以在这里看到 Sequelize 生成的 SQL 查询:

You can see the SQL query that is generated by Sequelize here:

SELECT `User`.*, `Groups`.`id` AS `Groups.id`, `Groups`.`name` AS `Groups.name`, `Groups`.`createdAt` AS `Groups.createdAt`, `Groups`.`updatedAt` AS `Groups.updatedAt`, `Groups.UsersGroups`.`createdAt` AS `Groups.UsersGroups.createdAt`, `Groups.UsersGroups`.`updatedAt` AS `Groups.UsersGroups.updatedAt`, `Groups.UsersGroups`.`facebookId` AS `Groups.UsersGroups.facebookId`, `Groups.UsersGroups`.`groupId` AS `Groups.UsersGroups.groupId`
FROM (
    SELECT `User`.`id`, `User`.`firstName`, `User`.`lastName`, `User`.`email`, `User`.`username`, `User`.`facebookId`, `User`.`password`, `User`.`createdAt`, `User`.`updatedAt` FROM `Users` AS `User`     WHERE `User`.`facebookId` = '1341052992643877' LIMIT 1) AS `User`
    LEFT OUTER JOIN (`UsersGroups` AS `Groups.UsersGroups`
    INNER JOIN `Groups` AS `Groups` ON `Groups`.`id` = `Groups.UsersGroups`.`groupId`
    )
ON `User`.`id` = `Groups.UsersGroups`.`facebookId`;

注意最后一行

ON `User`.`id` = `Groups.UsersGroups`.`facebookId`

必须

ON `User`.`facebookId` = `Groups.UsersGroups`.`facebookId`

推荐答案

原来我必须指定 facebookId 作为我的用户模型中的主键

Turns out I had to specify facebookId as a primary key in my user model

    facebookId: {
      type: DataTypes.STRING,
      primaryKey: true,
    },

这篇关于如何在Sequelize中查询多对多关系数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！