Answer a question

consider following Prisma schema:

model Conversation {
  id           Int                         @id @default(autoincrement())
  createdAt    DateTime                    @db.Timestamp(6)
  messages     ConversationMessage[]
}

model ConversationMessage {
  id             Int                     @id @default(autoincrement())
  text           String                  @db.VarChar(1000)
  sentAt         DateTime                @map("sent_at") @db.Timestamp(6)
  conversationId Int?                    @map("conversation_id")
  userId         Int?                    @map("user_id")
  conversation   Conversation?           @relation(fields: [conversationId], references: [id])
  sender         User?                   @relation(fields: [userId], references: [id])
}

I want to run such query so that I get a list of conversations ordered by date of their messages, i.e. the ones with new messages first.

prisma.conversation.findMany({
    orderBy: {
        messages: {
            sentAt: 'desc'
        }
    },
    ...
})

But the only way that I can query now is like this, i.e. relation has only _count property somehow.

prisma.conversation.findMany({
    orderBy: {
        messages: {
           '_count': 'desc'
        }
     },
     ...
})

Environment & setup

    OS: Mac OS,
    Database: PostgreSQL
    Node.js version: v12.19.0

Prisma Version

prisma               : 2.24.1
@prisma/client       : 2.24.1
Current platform     : darwin
Query Engine         : query-engine 18095475d5ee64536e2f93995e48ad800737a9e4 (at node_modules/@prisma/engines/query-engine-darwin)
Migration Engine     : migration-engine-cli 18095475d5ee64536e2f93995e48ad800737a9e4 (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core 18095475d5ee64536e2f93995e48ad800737a9e4 (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary        : prisma-fmt 18095475d5ee64536e2f93995e48ad800737a9e4 (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash : 18095475d5ee64536e2f93995e48ad800737a9e4
Studio               : 0.397.0
Preview Features     : orderByRelation

Thank You!

Answers

While Prisma V2.19 introduced sort by relation aggregate value, as of this writing, the only aggregate property supported is count. To the best of my knowledge, what you are asking for is not directly supported by Prisma at the moment. It would be possible if they add min and max aggregate properties for sorting.

A possible workaround is to sort the messages inside Node.js after retrieval. I'm adding a solution that uses the orderByRelation preview feature to simplify the sorting and ensure the messages in a conversation are always ordered (newest first).

Updating Prisma Client to use orderByRelation preview feature.

First, update schema.prisma to add the preview feature

generator client {
  provider = "prisma-client-js"
  previewFeatures = ["orderByRelation"]
}

Now update the prisma client

prisma generate client   

Get conversations and sort them by most recent message

// Assuming inside an async function 

let unsortedConversations = await prisma.conversation.findMany({
    include: {
        messages: {
            orderBy: {    
                sentAt: 'desc'  // messages for each converastion will be ordered newest first. 
            }
        }
    },
    // other conditions
})

unsortedConversations contains all required conversations, but they are unordered. You can sort it in the desired order by creating a custom comparator function.

function conversationComparatorFunction(conversationA, conversationB) {
    // Conversations with 0 messages will be placed last in arbitrary order. 
    if (!conversationB.messages.length) return 1;  
    if (!conversationA.messages.length) return -1;
    
    // sort conversations based on sentAt date of the first message. 
    // since messages were previously sorted, messages[0] always contain the most recent message. 
    if (conversationA.messages[0].sentAt > conversationB.messages[0].sentAt) {
        return -1;
    } else if (conversationA.messages[0].sentAt < conversationB.messages[0].sentAt) {
        return 1;
    } else return 0;

}

let sortedConversations = unsortedConversations.sort(conversationComparatorFunction)

Be warned though, if the number of Conversation records is very large sorting on the application side could lead to poor performance, especially considering Node.js is single-threaded.