javascript - Sequelize：多个 belongsTo 和 hasMany 问题

Question

我的架构由用户和评论组成。基本上，每个用户都可以对另一个用户发表评论。由于我需要从仅被评论的用户那里获得所有评论，因此我编写了以下关联：

User.hasMany(Review, { as: 'reviews', foreignKey: { name: 'reviewee', allowNull: false } });
Review.belongsTo(User, { as: 'user', foreignKey: { name: 'reviewee', allowNull: false } });
Review.belongsTo(User, { as: 'author', foreignKey: { name: 'reviewer', allowNull: false } });

当我尝试获取用户评论时

User.findById(1, {
  include: [{
    model: Review,
    as: 'reviews'
  }]
}).then((user) => {
  console.log(user.reviews);
});

它给出了这个错误

SequelizeDatabaseError: SQLITE_ERROR: no such column: reviews.reviewee

编辑：这里是模型

const User = sequelize.define('User', {
  id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
  username: { type: Sequelize.STRING, allowNull: false, unique: true },
  email: { type: Sequelize.STRING, allowNull: false, unique: true },
  phone_number: { type: Sequelize.STRING, allowNull: false, unique: true },
  last_login: { type: Sequelize.DATE, allowNull: false, defaultValue: Sequelize.NOW },
  password: { type: Sequelize.STRING, allowNull: false },
  verified_email: { type: Sequelize.BOOLEAN, allowNull: false, defaultValue: false },
  verified_phone: { type: Sequelize.BOOLEAN, allowNull: false, defaultValue: false },
  verified_id: { type: Sequelize.BOOLEAN, allowNull: false, defaultValue: false },
  password_change_date: { type: Sequelize.DATE, allowNull: false, defaultValue: Sequelize.NOW },
  role: { type: Sequelize.ENUM('admin', 'user'), allowNull: false, defaultValue: 'user' }
});

const Review = sequelize.define('Review', {
  id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
  text: { type: Sequelize.STRING, allowNull: false },
});

标签： javascriptsequelize.js

Answer 1

这可以解决问题。我只是依赖 belongsTo 的默认实现。

const Sequelize = require('sequelize');
const sequelize = new Sequelize('user', 'pwd', 'localhost', {
    dialect: 'sqlite',
    operatorsAliases: false,
    pool: {
        max: 5,
        min: 0,
        acquire: 30000,
        idle: 10000
    },
    storage: 'users-review.sqlite'
});

const User = sequelize.define('user', {
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    name: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true
    },
    password: {
        type: Sequelize.STRING,
        allowNull: false
    }
});

const Review = sequelize.define('review', {
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    text: {
        type: Sequelize.TEXT,
        allowNull: false
    }
});

User.hasMany(Review, {
    foreignKey: 'revieweeId',
    allowNull: false
});

Review.belongsTo(User, {
    as: 'reviewer'
});

Review.belongsTo(User, {
    as: 'reviewee'
});

sequelize.sync({
    force: true
}).then(() =>
    // create some users
    User.bulkCreate([
        { name: 'user1', password: 'pwd1' },
        { name: 'user2', password: 'pwd2' }
    ])
    .then(() => {
        return User.findAll();
    })
    // and set the associations
    .then(users => {
        let [ user1, user2 ] = users;
        return Review.create({
            text: 'about user2'
        })
        .then(review => {
            return review.setReviewer(user1).then(review => {
                return review.setReviewee(user2).then(review => {
                    return user1.addReview(review).then(() => {
                        return review;
                    });
                })
            })
        })
    })
    .then(review => {
        review.getReviewer().then(u => console.log(
            'reviewer', u.toJSON()));
        review.getReviewee().then(u => console.log(
            'reviewee', u.toJSON()));
    })
    .then(() => {
        return User.findById(1, {
            include: [ Review ]
        });
    })
    .then(user => {
        console.log('user', user.toJSON());
        user.getReviews().then(reviews =>
            reviews.forEach(r =>
                console.log("review", r.toJSON())));
    }));