Prisma•2mo ago

Order Conversation based on Chat `createdAt`?

I’m building a chat application using Prisma (PostgreSQL) for my backend, and I need to order conversations by the latest message's createdAt timestamp. However, I’m struggling to find a way to achieve this without adding an additional field like latestMessageCreatedAt to the Conversation model. Here's my Prisma schema for Conversation and Message:

model Conversation {
  id        Int       @id @default(autoincrement())
  uid       String    @unique @default(uuid())
  participants User[]
  message   Message[]
  

  createdAt DateTime  @default(now())
  updatedAt DateTime? @updatedAt
}

model Message {
  id             Int           @id @default(autoincrement())
  uid            String        @unique @default(uuid())
  message        String
  conversation   Conversation? @relation(fields: [conversationId], references: [id])
  conversationId Int?
  user           User?         @relation(fields: [userId], references: [id])
  userId         Int?

  createdAt DateTime  @default(now())
  updatedAt DateTime? @updatedAt
}

model Conversation {
  id        Int       @id @default(autoincrement())
  uid       String    @unique @default(uuid())
  participants User[]
  message   Message[]
  

  createdAt DateTime  @default(now())
  updatedAt DateTime? @updatedAt
}

model Message {
  id             Int           @id @default(autoincrement())
  uid            String        @unique @default(uuid())
  message        String
  conversation   Conversation? @relation(fields: [conversationId], references: [id])
  conversationId Int?
  user           User?         @relation(fields: [userId], references: [id])
  userId         Int?

  createdAt DateTime  @default(now())
  updatedAt DateTime? @updatedAt
}

My Prisma query to fetch conversations:

const conversations = await this.prismaService.conversation.findMany({
  where: {
    participants: {
      some: {
        uid: userId,
      },
    },
  },
  include: {
    participants: {
      select: {
        uid: true,
        name: true,
        profilePicture: true,
      },
    },
    message: {
      orderBy: {
        createdAt: 'desc',
      },
      take: 1,
      select: {
        message: true,
        createdAt: true,
      },
    },
  },
  orderBy: {
    createdAt: 'desc', // I want this to order by the latest message's createdAt, not conversation's
  },
});

const conversations = await this.prismaService.conversation.findMany({
  where: {
    participants: {
      some: {
        uid: userId,
      },
    },
  },
  include: {
    participants: {
      select: {
        uid: true,
        name: true,
        profilePicture: true,
      },
    },
    message: {
      orderBy: {
        createdAt: 'desc',
      },
      take: 1,
      select: {
        message: true,
        createdAt: true,
      },
    },
  },
  orderBy: {
    createdAt: 'desc', // I want this to order by the latest message's createdAt, not conversation's
  },
});

How can I order conversations by the latest message's createdAt without modifying the schema to add a new field? Is there a way to achieve this in Prisma using a query or aggregation?

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

Order Conversation based on Chat `createdAt`?