Retrieving chat history between two users sequelize

Question

I need help with how to retrieve chat history between two users in sequelize.

I currently have two tables; one for users and the other for messages.

My User table has the basic information like username, email and password while my Message table has information like senderId, receiverId and message

Below is the code for my models.

Message Model

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
return queryInterface.createTable('Messages', {
  id: {
    allowNull: false,
    autoIncrement: true,
    primaryKey: true,
    type: Sequelize.INTEGER
  },
  receiverId: {
    type: Sequelize.INTEGER,
    onDelete: 'CASCADE',
    references: {
      model: 'Users',
      key: 'id',
      as: 'receiverId',
    }
  },
  identifier: {
    type: Sequelize.INTEGER,
    defaultValue: 0
  },
  senderId: {
    type: Sequelize.INTEGER,
    onDelete: 'CASCADE',
    references: {
      model: 'Users',
      key: 'id',
      as: 'senderId',
    }
  },
  message: {
    type: Sequelize.TEXT
  },
  createdAt: {
    allowNull: false,
    type: Sequelize.DATE
  },
  updatedAt: {
    allowNull: false,
    type: Sequelize.DATE
  }
    });
   },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Messages');
   }
   };

User model

'use strict';
module.exports = (sequelize, DataTypes) => {
  var User = sequelize.define('User', {
username: {
  type: DataTypes.STRING,
  unique: true
},
email: {
  type: DataTypes.STRING,
  unique: true
    },
    password: DataTypes.STRING
  }, {
     classMethods: {
    associate: function (models) {
      // associations can be defined here
      User.hasMany(models.Message, {
        onDelete: 'CASCADE',
        foreignKey: 'senderId'
      });

      User.hasMany(models.Message, {
        onDelete: 'CASCADE',
        foreignKey: 'receiverId'
      });
        }
      }
    });
  return User;
};

What I need help with is how to retrieve the chat history between two users.

Answer 1

I had an issue like this, I was able to fix it by generating a unique identifier for the two users. When user A sends a message to user B, I will combine their userId together and store in the database. e.g '234:123' then I will store it in the chat table inside the identifier column.

Something you need to understand is to make sure you store the id in ascending order. e.g if user A with an id of 3 sent a message to user B with an id of 2, when you want to store their identifier in the database, make sure you store it as '3:2' the bigger Id will come first. Whenever you want to retrieve their chat history, just use their identifier and again make sure the bigger iD comes first.