nodejs/sequelize/many_to_many_same_model.js

#!/usr/bin/env node

// https://cirosantilli.com/sequelize-example

const assert = require('assert');
const path = require('path');
const { DataTypes } = 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', {
  username: { type: DataTypes.STRING },
}, {});
User.belongsToMany(User, { through: 'UserFollowUser', as: 'Follows', foreignKey: 'UserId' });
// This is ony needed for the function followedFindAll. "foreignKey" could be removed otherwise.
User.belongsToMany(User, { through: 'UserFollowUser', as: 'Followed', foreignKey: 'FollowId' });
await sequelize.sync({ force: true });

// Create some users.

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

// Make user0 follow user1 and user2
await user0.addFollows([user1, user2])
// Make user2 and user3 follow user0
await user2.addFollow(user0)
await user3.addFollow(user0)

let rows

// Get users followed by user0.
rows = await user0.getFollows({order: [['username', 'ASC']]})
common.assertEqual(rows, [
  { username: 'user1' },
  { username: 'user2' },
])

// Reverse order.
rows = await user0.getFollows({order: [['username', 'DESC']]})
common.assertEqual(rows, [
  { username: 'user2' },
  { username: 'user1' },
])

// Get users followed by user1.
rows = await user1.getFollows({order: [['username', 'ASC']]})
common.assertEqual(rows, [])

// Get users followed by user2.
rows = await user2.getFollows({order: [['username', 'ASC']]})
common.assertEqual(rows, [
  { username: 'user0' },
])

// Get users followed by user3.
rows = await user3.getFollows({order: [['username', 'ASC']]})
common.assertEqual(rows, [
  { username: 'user0' },
])

// Get users followed by an user by username instead of user object using findOne.
// Also get rid of all useless fields from the through table.
// followedFindAll is almost always a better alternative to this.
async function followedFindOne(username, opts={}) {
  return (await User.findOne(Object.assign({
    where: { username },
    attributes: [],
    include: [{
      model: User,
      as: 'Follows',
      through: {attributes: []},
    }],
    order: [['Follows', 'username', 'ASC']]
  }, opts))).Follows
}
rows = await followedFindOne('user0')
common.assertEqual(rows, [
  { username: 'user1'},
  { username: 'user2'},
])
// TODO how to implement limit in this case? We managed it in followedFindAll however.
// Does not work like this because the limit would be applied to the outer findOne.
//rows = await followedFindOne('user0', { limit: 1 })
//common.assertEqual(rows, [
//  { username: 'user1'},
//])
// Reverse order.
rows = await followedFindOne('user0', { order: [['Follows', 'username', 'DESC']] })
common.assertEqual(rows, [
  { username: 'user2'},
  { username: 'user1'},
])

// Same but with findAll. This is the preferred approach in most real world applications.
// Get users followed by an user by username instead of user object using findAll.
// Note that for this to work, we needed to add the extra relation:
// ``
// User.belongsToMany(User, { through: 'UserFollowUser', as: 'Followed', foreignKey: 'FollowId' });
// ``
async function followedFindAll(username, opts={}) {
  return User.findAll(Object.assign({
    include: [{
      model: User,
      as: 'Followed',
      attributes: [],
      through: { attributes: [] },
      where: { username },
    }],
    // Required for limit to work.
    subQuery: false,
    order: [['username', 'ASC']]
  }, opts))
}
rows = await followedFindAll('user0')
common.assertEqual(rows, [
  { username: 'user1'},
  { username: 'user2'},
])
rows = await followedFindAll('user0', { limit: 1 })
common.assertEqual(rows, [
  { username: 'user1'},
])
rows = await followedFindAll('user0', { order: [['username', 'DESC']] })
common.assertEqual(rows, [
  { username: 'user2'},
  { username: 'user1'},
])

// Now the inverse: find users that follow a given user.

// Find users that follow a given user by username.
// TODO Any way with the autogenerated methods? Anyways, this is good enough, almost always what we need.
// * https://github.com/sequelize/sequelize/issues/8263
// * https://stackoverflow.com/questions/27065154/how-to-get-all-children-or-parents-in-a-many-to-many-association-if-one-model-re
async function following(username, opts={}) {
  return User.findAll(
    Object.assign({
      include: [{
        model: User,
        as: 'Follows',
        where: { username },
        attributes: [],
        through: { attributes: [] }
      }],
      // subQuery is mandatory if you want to apply a limit, like in every real query.
      subQuery: false,
      order: [['username', 'ASC']],
    }, opts)
  )
}
common.assertEqual(await following('user0'), [
  { username: 'user2' },
  { username: 'user3' },
])
common.assertEqual(await following('user0', { order: [['username', 'DESC']] }), [
  { username: 'user3' },
  { username: 'user2' },
])
common.assertEqual(await following('user0', { limit: 1 }), [
  { username: 'user2' },
])
common.assertEqual(await following('user0', { limit: 1, order: [['username', 'DESC']] }), [
  { username: 'user3' },
])
common.assertEqual(await following('user1'), [
  { username: 'user0' },
])
common.assertEqual(await following('user2'), [
  { username: 'user0' },
])
common.assertEqual(await following('user3'), [])

// has methods
assert(!await user0.hasFollow(user0))
assert(!await user0.hasFollow(user0.id))
assert( await user0.hasFollow(user1))
assert( await user0.hasFollow(user2))
assert(!await user0.hasFollow(user3))

// Count method
assert.strictEqual(await user0.countFollows(), 2)

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