My schema consists of users and reviews. Basically, each user can leave a review on another user. Since I need to get all reviews from a user that was reviewed only, I wrote the following associations:

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 } });

When I try to get user reviews

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

it gives this error

SequelizeDatabaseError: SQLITE_ERROR: no such column: reviews.reviewee

EDIT: Here are the models

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 },
});

My schema consists of users and reviews. Basically, each user can leave a review on another user. Since I need to get all reviews from a user that was reviewed only, I wrote the following associations:

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 } });

When I try to get user reviews

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

it gives this error

SequelizeDatabaseError: SQLITE_ERROR: no such column: reviews.reviewee

EDIT: Here are the models

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 },
});

• javascript
• sequelize.js

• Does your user table contains reviewee and reviewer. Please show us your database schema. – faizan baig Commented May 23, 2018 at 7:42
• @faizanbaig no, it doesn't, edited the question – Kudayar Pirimbaev Commented May 26, 2018 at 8:55
• Your model is not correct as per the relationship will give an answer by the EOD when i reach home. Will update my answer with migration file and model file – faizan baig Commented May 26, 2018 at 11:07

1 Answer 1

This does the trick. I just rely on the default implementation of 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())));
    }));