javascript - Schema design for chat app with group chat. I can't query chat room in chronological order

Question

I'm working on a chat app that allows group app. In order for me to achieve group chat feature, I added a table called ChatRoomUser that connects ChatRoom and Message tables. I got everything working but can't figure out to query Chat Room in a chronological order.

query getUser(id: "USER_ID"){
  chatRoomUser(sortDirection: DESC) {  // Can't query this in chronological order...
    items {
      id
      chatRoom{
        id
        receiverHasRead
        createdBy
      }
    }
  }
}

I was going to update updatedAt under ChatRoomUser when there's a new message in a chat room but that won't be efficient when there's many users in a room. Because I will have to update 100 rows if there's 100 users in a chat room.

What will be the best way to solve this issue?

type User 
  @model 
  @auth(rules: [
      { allow: private }
    ]
  ) {
  id: String!
  chatRoomUser: [ChatRoomUser] @connection(name: "UserChatRoomUser", sortField: "updatedAt") # this does not seem like a good way to query
}

type ChatRoomUser
  @model
  @key(name: "gsi-doesChatRoomExist", fields: ["chatRoomUserUserId", "members"], queryField: "doesChatRoomExist")
  @auth(rules: [
      { allow: private }
    ]
  ) {
  id: ID!
  chatRoomUserUserId: String!
  chatRoomUserChatRoomId: String!
  members: String! 

  user: User @connection(name: "UserChatRoomUser")
  chatRoom: ChatRoom @connection(name: "ChatRoomUserChatRoom")

  createdAt: AWSDateTime
  updatedAt: AWSDateTime # I was going to use this as sortField
}

type ChatRoom 
  @model
  @auth(rules: [
      { allow: private }
    ]
  ) {
  id: ID!
  createdBy: String!
  receiverHasRead: Boolean!
  
  chatRoomUsers: [ChatRoomUser] @connection(name: "ChatRoomUserChatRoom")
  messages: [Message] @connection(name: "chatByChatRoom", sortField: "createdAt")

  createdAt: AWSDateTime
  updatedAt: AWSDateTime
}

type Message
@model
@auth(rules: [
    { allow: private }
  ]
) { 
  id: ID!
  content: String!
  messageChatRoomId: String!
  messageUserId: String!

  user: User @connection(name: "UserMassages")
  chatRoom: ChatRoom @connection(name: "chatByChatRoom")

  createdAt: AWSDateTime!
  updatedAt: AWSDateTime
}

I have a feeling that I need to change schema design to make this work but can't think of any solution that can work.

Answer 1

You'll want to implement a key that lets you query your Chatroom for Messages by createdAt. Check out this page on defining keys for more information.