CODEWITHSUNDEEP
nestedsequelize.jssql-order-by
Derrick Mei
Derrick Mei

Reputation: 149

Sequelize: Ordering by Nested Associations

I have 4 tables: 

ChatRooms
Participants
Messages
Users
  1. ChatRoom has many Participants
  2. ChatRoom has many Messages
  3. User has many Messages
  4. User has Many Participants
  5. Participant belongs to User
  6. Message belongs to User

I'm trying to use Sequelize to query Users and under the Participants relation, I want it to return a list of 10 Participants(which is essentially the user) ordered by the ChatRoom with the most recent message. Essentially Participants would be the most recently active ChatRoms.

//   My current code is:

User.findAll({
        include: [
            {
                model: Participant,
                include: [
                    {
                        model: ChatRoom,
                        include: [{ model: Message}]
                    }
                ],
                order: [[ChatRoom, Message, 'id', 'DESC']]
            }
        ],
    })

// This is what my server is returning right now, 
// but the ordering is not working:
// participantId: 2 should be on top as it has a more recent message
{
    userId: 1,
    name: 'Kevin',
    participants: [
        {
            participantId: 1,
            userId: 1,
            chatRoomId: 1,
            chatRoom:
            {
                chatRoomId: 1,
                Messages: [{
                    MessageId: 1,
                    message: 'message1',
                    userId: 1
                },
                {
                    MessageId: 2,
                    message: 'message2',
                    userId: 2
                }]
            }
        },
        {
            participantId: 2,
            userId: 1,
            chatRoomId: 2,
            chatRoom:
            {
                chatRoomId: 2,
                Messages: [{
                    MessageId: 3,
                    message: 'message3',
                    userId: 1
                },
                {
                    MessageId: 4,
                    message: 'message4',
                    userId: 3
                }]
            }
        }
    ]
}

I don't know if what I want is possible. The important part is that the query returns the ChatRooms with the most recent messages. I don't have to do it inside the User object. I can do it in a separate query if need be and I am also open to designing the schema a different way.

Upvotes: 3

Views: 3291

Answers (2)

Yash Shah
Yash Shah

Reputation: 302

Here you should try this.

Add > separate:true in your join with include and after that your code should be like below 

User.findAll({
    subQuery:false
      include: [
      {
          model: Participant,
          include: [
          {
              model: ChatRoom,
              include: [{ model: Message}],
              order: [['id', 'DESC']]
          }
          ],
        }
    ],
})

Upvotes: -1

Derrick Mei
Derrick Mei

Reputation: 149

I discovered that my posted code does work. The issue is that when I used limit (which I had left out for simplicities sake) it does a subquery and causes it to have a "column not found" error.

I tried a subQuery: false but it still didn't allow me to combine a limit with order.

Upvotes: 4

Related Questions