I've table containing messages between users.
{ message, senderId, receiverId, createdAt, updatedAt, id }
For given User, I want to return a list of people that user has ever talked to and sorted by date of last message from either one whichever is latest.
Person D, Person A, Person C, Person Z, ....
User column has these: { name, createdAt, updatedAt, id }
Query for all Messages and use the Op.or query filter to find records where your user id matches either the send sender or receiver, then order by the createdAt in descending order. Use raw to avoid creating Instance objects, then loop over the results and collect the user IDs by reducing the messages array.
const userId = ???;
const messages = await Message.findAll({
attributes: ['senderId', 'receiverId', 'createdAt'],
where: {
[Op.or]: {
senderId: userId,
receiverId: userId,
},
},
order: [['createdAt', 'DESC']],
raw: true, // get raw since we don't need to bother creating Instances
});
// use an object to collect unique IDs
const userIds = {};
// reduce messages to collect userIds and createdAt
const userIds = messages.reduce((userIds, message) => {
// check the senderId and recieverId
['senderId', 'recieverId'].forEach((column) => {
// if it's not our userId and not already in the list, add it
if (message[column] !== userId && !userIds[message[column]]) {
// you could check things about message.createdAt here, or use JSON for more info
userIds[message[column]] = message.createdAt;
}
});
// return the accumulator
return userIds;
}, {});
console.log('User IDs', userIds);
