Prisma, how to get order by a field in a relation?

Question

I'm learning how to use Prisma, but I'm stuck on a rather simple problem. Let's say I have these entities (simplified):

model User { 
    id        Int       @id @default(autoincrement())
    email     String    @unique
    password  String
    name      String
    avatar    String
    createdAt DateTime  @default(now())
    updatedAt DateTime  @updatedAt
    UserInfo  UserInfo?
}

model UserInfo {
    id        Int     @id @default(autoincrement())
    user_id   Int     @unique
    user      User    @relation(fields: \[user_id\], references: \[id\])
    is_active Boolean @default(false)
}

And I want to retrieve all users ordered by the is_active field in the UserInfo table and then by another field in the User table. The UserInfo might not be populated for some users.

To be clear, this is the query I want to achieve:

SELECT * FROM User u 
FULL JOIN UserInfo ui ON u.id = ui.user_id
ORDER BY ui.is_active DESC, u.name

I've tried the following approaches (and some others that i can't find right now), but with no luck:

const result = await prisma.user.findMany({
  include: {
    UserInfo: {
      orderBy: {
        is_active: 'desc',
      },
    },
  },
});

const result = await prisma.user.findMany({
  select: {
    // [...fields]
    userInfo: {
      select: {
        // [...fields]
      },
      orderBy: {
        is_active: 'desc',
      },
    },
  },
  orderBy: [
    {
      is_active: 'desc',
    },
    {
      name: 'desc',
    },
  ],
});

What am I missing? Am I doing something wrong?

i feel like i'm missing something really dumb, or i did not understand the documentation properly...

Any help would be greatly appreciated!

Thank you in advance for your assistance!

Answer 1

I feel like you already found the answer, but yes, it is possible and actually your code seems correct and it should work.

This is what works perfectly in my use case:

  const response = await prisma.configuration.findUnique({
    where: { id: configuration.id },
    include: {
      water_tanks: {
        orderBy: {
          id: "asc",
        },
      },
      wash_bays: {
        orderBy: {
          id: "asc",
        },
      },
    },
  });