Adding orderBy to Nested partial fields select in the relation table in SQL query.findMany

Question

I'm implementing a chat room and I want to direct to the latest updated chat room. Here's how I get the chat room:

import { eq,asc,desc } from "drizzle-orm";

import { db } from "@/db";
import { usersTable,documentsTable, usersToDocumentsTable } from "@/db/schema";
export const getDocuments = async (userId: string) => {
  "use server";

  const documents = await db.query.usersToDocumentsTable.findMany({
    where: eq(usersToDocumentsTable.userId, userId),
    with: {
      document: {
        columns: {
          displayId: true,
          title: true,
          latestText:true,
          pinnedMessageId:true,
          retrivedMessage:true,
          latestUpdatedAt:true,
        },
        orderBy:[asc(document.columns.latestUpdatedAt)],//here's the problem
      },
    },
  });
  return documents;
};

A document is a chat room, and I want to add orderBy to all the chat room by the latest update time. But I don't know the syntax. Documentation of drizzle give an example but I still have no idea how to write. Link to documentation: https://orm.drizzle.team/docs/rqb#order-by

I've also tried to put orderBy inside the column, but it still can't work.

I want to know if it is available to order the documents by the variables in the columns and how to do it. Thanks!

Answer 1

Use the desc function from your database library to arrange the result set according to the latestUpdatedAt column in descending order (latest first).

This is how to change your code, in this updated code I replaced OrderBy: [asc(document.columns.latestUpdatedAt)] with orderBy: [desc(document.columns.latestUpdatedAt)] . You'll get the latest chat rooms first because this will sort the result set according to the latestUpdatedAt column in descending order.

import { eq, desc } from "drizzle-orm";

import { db } from "@/db";
import { usersTable, documentsTable, usersToDocumentsTable } from "@/db/schema";

export const getDocuments = async (userId: string) => {
  "use server";

  const documents = await db.query.usersToDocumentsTable.findMany({
    where: eq(usersToDocumentsTable.userId, userId),
    with: {
      document: {
        columns: {
          displayId: true,
          title: true,
          latestText: true,
          pinnedMessageId: true,
          retrivedMessage: true,
          latestUpdatedAt: true,
        },
        orderBy: [desc(document.columns.latestUpdatedAt)], // Order by latestUpdatedAt in descending order
      },
    },
  });

  return documents;
};

Hope it's helpful :)