Order results by a nested relation's DateTime with Prisma

I have a

Conversation

Conversation

model which has one-to-many relationship with

Message

Message

s. I want to order the conversations by the most recent message, but it seems Prisma doesn't have a way to do this??? I need pagination so I can't do this after querying either, so is my only solution to write the SQL myself? I get the love for Prisma, but there are so many things missing

AmosOP•10/5/22, 1:29 PM

That's the other way around

AmosOP•10/5/22, 1:29 PM

Using that example I would want to sort an author by their latest post

AmosOP•10/5/22, 2:47 PM

I had to do

I suck at SQL so I have no idea if this is correct or not but it seems to work (haven't tried the pagination part yet)

const limit = input.limit ?? 25;
const { cursor } = input;

const offset = cursor ? Prisma.sql`OFFSET ${cursor}` : Prisma.empty;

const conversations = await ctx.prisma.$queryRaw<(Conversation & Message)[]>`
  SELECT c.id, c.subject, m.authorName, m.body, m.bodyHtml, m.createdUtc, m.isExternal
  FROM (
    SELECT lm.conversationId, MAX(lm.createdUtc) AS created
    FROM Conversation as lc
    JOIN Message as lm ON lm.conversationId = lc.id
    GROUP BY lm.conversationId
  ) as l
  JOIN Conversation as c ON c.id = l.conversationId
  JOIN Message as m ON m.conversationId = l.conversationId AND m.createdUtc = l.created
  ORDER BY m.createdUtc DESC
  LIMIT ${limit + 1}
  ${offset};
`;

let nextCursor: number | undefined = undefined;

if (conversations.length > limit) {
  conversations.pop();
  nextCursor = (cursor + 1) * limit;
}

return {
  conversations,
  nextCursor,
};
`;

let nextCursor: typeof cursor | undefined = undefined;

if (conversations.length > limit) {
  const nextItem = conversations.pop();
  nextCursor = nextItem!.createdAt;
}

const limit = input.limit ?? 25;
const { cursor } = input;

const offset = cursor ? Prisma.sql`OFFSET ${cursor}` : Prisma.empty;

const conversations = await ctx.prisma.$queryRaw<(Conversation & Message)[]>`
  SELECT c.id, c.subject, m.authorName, m.body, m.bodyHtml, m.createdUtc, m.isExternal
  FROM (
    SELECT lm.conversationId, MAX(lm.createdUtc) AS created
    FROM Conversation as lc
    JOIN Message as lm ON lm.conversationId = lc.id
    GROUP BY lm.conversationId
  ) as l
  JOIN Conversation as c ON c.id = l.conversationId
  JOIN Message as m ON m.conversationId = l.conversationId AND m.createdUtc = l.created
  ORDER BY m.createdUtc DESC
  LIMIT ${limit + 1}
  ${offset};
`;

let nextCursor: number | undefined = undefined;

if (conversations.length > limit) {
  conversations.pop();
  nextCursor = (cursor + 1) * limit;
}

return {
  conversations,
  nextCursor,
};
`;

let nextCursor: typeof cursor | undefined = undefined;

if (conversations.length > limit) {
  const nextItem = conversations.pop();
  nextCursor = nextItem!.createdAt;
}