nodejs/sequelize/one_to_many.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 () => {
const Comment = sequelize.define('Comment', {
  body: { type: DataTypes.STRING },
});
const User = sequelize.define('User', {
  name: { type: DataTypes.STRING },
});
User.hasMany(Comment)
Comment.belongsTo(User)
let u0, u1, rows
async function reset(UserModel=User, CommentModel=Comment) {
  await sequelize.sync({force: true});
  u0 = await UserModel.create({name: 'u0'})
  u1 = await UserModel.create({name: 'u1'})
  await CommentModel.create({body: 'u0c0', UserId: u0.id});
  await CommentModel.create({body: 'u0c1', UserId: u0.id});
  await CommentModel.create({body: 'u1c0', UserId: u1.id});
}
await reset()

// Direct way.
{
  const u0Comments = await Comment.findAll({
    where: { UserId: u0.id },
    order: [['id', 'ASC']],
  });
  assert.strictEqual(u0Comments[0].body, 'u0c0');
  assert.strictEqual(u0Comments[1].body, 'u0c1');
  assert.strictEqual(u0Comments[0].UserId, u0.id);
  assert.strictEqual(u0Comments[1].UserId, u0.id);
  // Not added as an object by default. Would require extra query.
  assert.strictEqual(u0Comments[0].User, undefined);
  assert.strictEqual(u0Comments[1].User, undefined);
}

// Include data from the other side of the association in the query.
{
  const u0Comments = await Comment.findAll({
    where: { UserId: u0.id },
    order: [['id', 'ASC']],
    include: User,
    // Equivalent alternatives in this case.
    //include: [User],
    //include: [{ model: User }],
  });
  assert.strictEqual(u0Comments[0].body, 'u0c0');
  assert.strictEqual(u0Comments[1].body, 'u0c1');
  assert.strictEqual(u0Comments[0].UserId, u0.id);
  assert.strictEqual(u0Comments[1].UserId, u0.id);
  // These did get added now.
  assert.strictEqual(u0Comments[0].User.name, 'u0');
  assert.strictEqual(u0Comments[1].User.name, 'u0');
}

// Nicer higher level way.
{
  const u0Comments = await u0.getComments({
    include: [{ model: User }],
    order: [['id', 'ASC']],
  });
  assert.strictEqual(u0Comments[0].body, 'u0c0');
  assert.strictEqual(u0Comments[1].body, 'u0c1');
  assert.strictEqual(u0Comments[0].User.name, 'u0');
  assert.strictEqual(u0Comments[1].User.name, 'u0');
}

// order in include.
// https://stackoverflow.com/questions/29995116/ordering-results-of-eager-loaded-nested-models-in-node-sequelize
{
  let u0WithComments = await User.findOne({
    where: { id: u0.id },
    order: [[
      'Comments', 'body', 'DESC'
      // Also works.
      //sequelize.col('Comments.body'), 'DESC'
    ]],
    include: [{
      model: Comment,
    }],
  })
  common.assertEqual(u0WithComments.Comments, [
    { body: 'u0c1' },
    { body: 'u0c0' },
  ])

  // Also limit.
  u0WithComments = await User.findOne({
    where: { id: u0.id },
    order: [[
      'Comments', 'body', 'DESC'
    ]],
    limit: 1,
    // TODO why needed.
    subQuery: false,
    include: [{
      model: Comment,
    }],
  })
  common.assertEqual(u0WithComments.Comments, [
    { body: 'u0c1' },
  ])
}

// If you REALLY wanted to not repeat the UserId magic constant everywhere, you could use User.associations.Comments.foreignKey
// But it is such a mouthful, that nobody likely ever uses it?
// https://stackoverflow.com/questions/34059081/how-do-i-reference-an-association-when-creating-a-row-in-sequelize-without-assum
{
  await Comment.create({body: 'u0c2', [User.associations.Comments.foreignKey]: u0.id});
  // Syntax that we really would like instead.
  //await Comment.create({body: 'u0c2', User: u0});
  assert.strictEqual(
    (await Comment.findAll({
      where: { [User.associations.Comments.foreignKey]: u0.id },
    })).length,
    3
  );
  await reset()
}

// DELETE does SET NULL by default, not CASCADE.
{
  await u0.destroy()
  const comments = await Comment.findAll({order: [['body', 'ASC']]})
  assert.strictEqual(comments[0].body, 'u0c0');
  assert.strictEqual(comments[0].UserId, null);
  assert.strictEqual(comments[1].body, 'u0c1');
  assert.strictEqual(comments[1].UserId, null);
  assert.strictEqual(comments[2].body, 'u1c0');
  assert.strictEqual(comments[2].UserId, u1.id);
  await reset()
}

// ON DELETE CASCADE
{
  await u0.destroy()
  const CommentCascade = sequelize.define('CommentCascade', {
    body: { type: DataTypes.STRING },
  }, {});
  User.hasMany(CommentCascade, { onDelete: 'CASCADE' })
  CommentCascade.belongsTo(User)
  await reset(User, CommentCascade)
  await u0.destroy()
  const comments = await CommentCascade.findAll({order: [['body', 'ASC']]})
  assert.strictEqual(comments[0].body, 'u1c0');
  assert.strictEqual(comments.length, 1);
  await reset()
}

// as aliases.
// Allows us to use a nicer name for a relation rather than the exact class name.
// E.g. here we name the User of a Comment as a "author".
// And mandatory do diambiguate multiple associations with a single type.
{
  const CommentAs = sequelize.define('CommentAs', {
    body: { type: DataTypes.STRING },
  }, {});
  const UserAs = sequelize.define('UserAs', {
    name: { type: DataTypes.STRING },
  }, {});
  UserAs.hasMany(CommentAs)
  CommentAs.belongsTo(UserAs, {as: 'author'})
  await sequelize.sync({force: true});
  const u0 = await UserAs.create({name: 'u0'})
  const u1 = await UserAs.create({name: 'u1'})
  await CommentAs.create({body: 'u0c0', authorId: u0.id});
  await CommentAs.create({body: 'u0c1', authorId: u0.id});
  await CommentAs.create({body: 'u1c0', authorId: u1.id});

  {
    const u0Comments = await CommentAs.findAll({
      where: { authorId: u0.id },
      order: [['id', 'ASC']],
      // Instead of include: UserAs
      include: 'author',
    });
    assert.strictEqual(u0Comments[0].body, 'u0c0');
    assert.strictEqual(u0Comments[1].body, 'u0c1');
    assert.strictEqual(u0Comments[0].authorId, u0.id);
    assert.strictEqual(u0Comments[1].authorId, u0.id);
    assert.strictEqual(u0Comments[0].author.name, 'u0');
    assert.strictEqual(u0Comments[1].author.name, 'u0');
  }

  // Trying with the higher level getter.
  {
    // TODO
    // u0.getComments is not a function
    //const u0Comments = await u0.getComments({
    //  include: 'author',
    //});
    //assert.strictEqual(u0Comments[0].body, 'u0c0');
    //assert.strictEqual(u0Comments[1].body, 'u0c1');
    //assert.strictEqual(u0Comments[0].author.name, 'u0');
    //assert.strictEqual(u0Comments[1].author.name, 'u0');
  }
}
})().finally(() => { return sequelize.close() });