So I am trying to build an endpoint for my front end to fetch the current user contacted user.

A contacted user is an user that either sent a message or recived one to the current user.

Now unfortunately I wasn't able to figure out how to use sequlize's associations for this so what I ended up doing was using a raw query for to get the right data out.

At the end of episode five, it turns out that

SELECT `id`,
       `email`,
       `created_at`
FROM `user` AS `User`
WHERE exists(select *
             from chat_message
             where (recipient_id = 4 and User.id = sender_id)
                or (sender_id = 4 AND User.id = chat_message.recipient_id) order by created_at asc);

Now that issue is that I also need to to order the users by the last chat_message's createdAt and since I used a subquery I can't access any columns from chat_message.

Is there a way to pull this data ordered?

This is a sql fidle with some test data.

CodePudding user response：

You need to use the same subquery in WHERE and ORDER BY clauses. Something like this:

const subQuery = Sequelize.literal(`(select max(chat_message.created_at)
             from chat_message
             where (chat_message.recipient_id = $userId and User.id = chat_message.sender_id)
                or (chat_message.sender_id = $userId AND User.id = chat_message.recipient_id))`)
const users = await User.findAll({
  where: sequelize.where(subQuery, Op.ne, 'null'),
  bind: {
    userId: 4
  },
  order: [[subQuery, 'ASC']]
})

By the way, you didn't indicate that chat_message has created_at column.