Theo's Typesafe Cult•3y ago

Order results by a nested relation's DateTime with Prisma

I have a Conversation model which has one-to-many relationship with Messages. 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 🙃

2 Replies

Unknown User•3y ago

Message Not Public

Amos•3y ago

That's the other way around Using that example I would want to sort an author by their latest post I had to do

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;
}

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)

Gaming

Programming

Order results by a nested relation's DateTime with Prisma