nodejs/sequelize/many_to_many_custom_table.js

#!/usr/bin/env node
// https://cirosantilli.com/sequelize-example
const assert = require('assert')
const { DataTypes, Op } = require('sequelize')
const common = require('./common')
const sequelize = common.sequelize(__filename, process.argv[2], { define: { timestamps: false } })
;(async () => {

// Create the tables.
const User = sequelize.define('User', {
  name: { type: DataTypes.STRING },
});
const Post = sequelize.define('Post', {
  body: { type: DataTypes.STRING },
});
const UserLikesPost = sequelize.define('UserLikesPost', {
  UserId: {
    type: DataTypes.INTEGER,
    references: {
      model: User,
      key: 'id'
    }
  },
  PostId: {
    type: DataTypes.INTEGER,
    references: {
      model: Post,
      key: 'id'
    }
  },
  score: {
    type: DataTypes.INTEGER,
  },
});
// UserLikesPost is the name of the relation table.
// Sequelize creates it automatically for us.
// On SQLite that table looks like this:
// CREATE TABLE `UserLikesPost` (
//   `createdAt` DATETIME NOT NULL,
//   `updatedAt` DATETIME NOT NULL,
//   `UserId` INTEGER NOT NULL REFERENCES `Users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
//   `PostId` INTEGER NOT NULL REFERENCES `Posts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
//   `Score` INTEGER,
//   PRIMARY KEY (`UserId`, `PostId`)
// );
User.belongsToMany(Post, {through: UserLikesPost});
Post.belongsToMany(User, {through: UserLikesPost});
await sequelize.sync({force: true});

// Create some users and likes.

const user0 = await User.create({name: 'user0'})
const user1 = await User.create({name: 'user1'})
const user2 = await User.create({name: 'user2'})

const post0 = await Post.create({body: 'post0'});
const post1 = await Post.create({body: 'post1'});
const post2 = await Post.create({body: 'post2'});

// Autogenerated add* methods

// Make some useres like some posts.
await user0.addPost(post0, {through: {score: 1}})
await user1.addPost(post1, {through: {score: 2}})
await user1.addPost(post2, {through: {score: 3}})

// Find what user0 likes.
const user0Likes = await user0.getPosts({order: [['body', 'ASC']]})
assert.strictEqual(user0Likes[0].body, 'post0');
console.error(user0Likes[0]);
assert.strictEqual(user0Likes[0].UserLikesPost.score, 1);
assert.strictEqual(user0Likes.length, 1);

// Find what user1 likes.
const user1Likes = await user1.getPosts({order: [['body', 'ASC']]})
assert.strictEqual(user1Likes[0].body, 'post1');
assert.strictEqual(user1Likes[0].UserLikesPost.score, 2);
assert.strictEqual(user1Likes[1].body, 'post2');
assert.strictEqual(user1Likes[1].UserLikesPost.score, 3);
assert.strictEqual(user1Likes.length, 2);

// Where on the custom through table column.
// Find posts that user1 likes which have score greater than 2.
// https://stackoverflow.com/questions/38857156/how-to-query-many-to-many-relationship-sequelize
{
  const rows = await Post.findAll({
    include: [
      {
        model: User,
        where: {id: user1.id},
        through: {
          where: {score: { [Op.gt]: 2 }},
        },
      },
    ],
  })
  assert.strictEqual(rows[0].body, 'post2');
  // TODO how to get the score here as well?
  //assert.strictEqual(rows[0].UserLikesPost.score, 3);
  assert.strictEqual(rows.length, 1);
}

// TODO: this doesn't work. Possible at all in a single addUsers call?
// Make user0 and user2 like post1
// This method automatically generated.
//await post1.addUsers(
//  [user0, user2],
//  {through: [
//    {score: 2},
//    {score: 3},
//  ]}
//)

})().finally(() => { return sequelize.close() });