prisma Order by relation has only _count property. Can not order by relation fields

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!

Timms

Answer 1

For such cases I use rawQuery method and write custom select e.g.

export async function getConversationsOrderedByMessageSentAt() {
  return db.$queryRaw`SELECT c.*
    FROM conversation c
    JOIN messages m
        ON c.id = m. conversationId
    ORDER BY m.sentAt DESC;`;
}

This approach does not require sorting on backend side, so performance is not affected.

Eastwood

Answer 2

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.

Arterial

Thanks! I resolved this similarly, but added sorting on the client. – Timms

Sad that this is still not supported :( – Arborvitae

Answer 3

A simple answer to this that doesn't use other packages, is to create a property:

lastChildUpdatedAt PS: name this as you want

When creating a new child, or updating one, updated this property on the parent too. Then, when fetching, use:

orderBy: {
  lastChildUpdatedAt: "desc",
}

Busy

I guess this is the way to do it in Prisma, but I'd rather like to have just a sort field of a joined table. I wonder why this is not supported. – Caracole

Answer 4

OrderBy relation is still a preview feature - you need to make sure to use the feature flag

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

https://www.prisma.io/docs/concepts/components/prisma-client/filtering-and-sorting#sort-by-relation-preview

Staggs