Theo's Typesafe Cult•2y ago

How would I order by the role?

Hello, I'm trying to set up an API, where I return some users. I've got it working, where it returns the right users, with their roles. But I would like to order them so the role "ADMIN" comes first. I know there is orderBy, but every time I use it, it just sends a bunch of errors. Maybe I'm on a completely wrong track? What I'm currently doing to get the user:

  const users = await prisma.user.findMany({
    skip: skip,
    take: perPage,
    where: {
      role: {
        some: {
          role: {
            in: ["ADMIN", "DEV"]
          }
        }
      }
    },
    include: {
      role: {
        where: {
          NOT: {
            role: 'USER'
          },
        }
      }
    }
  });

  const users = await prisma.user.findMany({
    skip: skip,
    take: perPage,
    where: {
      role: {
        some: {
          role: {
            in: ["ADMIN", "DEV"]
          }
        }
      }
    },
    include: {
      role: {
        where: {
          NOT: {
            role: 'USER'
          },
        }
      }
    }
  });

30 Replies

Neto•2y ago

can you show the error?

BørgeOP•2y ago

Well i tried this:

    orderBy: {
      role: {
        asc: true
      }
    }

    orderBy: {
      role: {
        asc: true
      }
    }

Error:

Unknown arg `asc` in orderBy.role.asc for type UserRoleOrderByRelationAggregateInput. Available args:

type UserRoleOrderByRelationAggregateInput {
  _count?: SortOrder
}

Unknown arg `asc` in orderBy.role.asc for type UserRoleOrderByRelationAggregateInput. Available args:

type UserRoleOrderByRelationAggregateInput {
  _count?: SortOrder
}

But I don't think I can use asc, as it's just a string I'm trying to check for?

Neto•2y ago

try this

orderBy: {
      role: 'asc'
    }

orderBy: {
      role: 'asc'
    }

BørgeOP•2y ago

Giving me this error:

Argument role: Got invalid value 'asc' on prisma.findManyUser. Provided String, expected UserRoleOrderByRelationAggregateInput:
type UserRoleOrderByRelationAggregateInput {
  _count?: SortOrder
}

Argument role: Got invalid value 'asc' on prisma.findManyUser. Provided String, expected UserRoleOrderByRelationAggregateInput:
type UserRoleOrderByRelationAggregateInput {
  _count?: SortOrder
}

Can it be ordered by ascending? Because what is it ascending to?

Neto•2y ago

can you show the whole query?

BørgeOP•2y ago

Do you mean this?

async function getUsers(page: number, perPage: number) {
  const skip = (page - 1) * perPage;
  const take = perPage;

  const users = await prisma.user.findMany({
    skip: skip,
    take: perPage,
    where: {
      role: {
        some: {
          role: {
            in: ["ADMIN", "UDVIKLER"]
          }
        }
      }
    },
    include: {
      role: {
        where: {
          NOT: {
            role: 'USER'
          },
        },
      },
    },
    orderBy: {
      role: 'asc'
    }
  });

  return users;
}

async function getUsers(page: number, perPage: number) {
  const skip = (page - 1) * perPage;
  const take = perPage;

  const users = await prisma.user.findMany({
    skip: skip,
    take: perPage,
    where: {
      role: {
        some: {
          role: {
            in: ["ADMIN", "UDVIKLER"]
          }
        }
      }
    },
    include: {
      role: {
        where: {
          NOT: {
            role: 'USER'
          },
        },
      },
    },
    orderBy: {
      role: 'asc'
    }
  });

  return users;
}

Neto•2y ago

yeah

BørgeOP•2y ago

But again what is it ordering by? Is there a way to make some custom orderBy?

Neto•2y ago

i have this setup for orderBy

Neto•2y ago

and worked fine

BørgeOP•2y ago

What comes first then? Staff or Partner?

Neto•2y ago

partner is role a separate relationship? or is a user prop? if is the first, being a separate relationship

BørgeOP•2y ago

This is my schema

model User {
  id            String    @id @default(cuid())
  discordId     String    @unique
  discriminator String
  avatar        String?
  name          String?
  email         String?   @unique()

  role         UserRole[]
  accounts      Account[]
  sessions      Session[]
}

model UserRole {
  id        String    @id @default(cuid())
  discordId String
  role      String     @default("USER")
  user      User     @relation(fields: [discordId], references: [discordId], onDelete: Cascade)

  @@index([discordId])
}

model User {
  id            String    @id @default(cuid())
  discordId     String    @unique
  discriminator String
  avatar        String?
  name          String?
  email         String?   @unique()

  role         UserRole[]
  accounts      Account[]
  sessions      Session[]
}

model UserRole {
  id        String    @id @default(cuid())
  discordId String
  role      String     @default("USER")
  user      User     @relation(fields: [discordId], references: [discordId], onDelete: Cascade)

  @@index([discordId])
}

Neto•2y ago

async function getUsers(page: number, perPage: number) {
  const skip = (page - 1) * perPage;
  const take = perPage;

  const users = await prisma.user.findMany({
    skip: skip,
    take: perPage,
    where: {
      role: {
        some: {
          role: {
            in: ["ADMIN", "UDVIKLER"]
          }
        }
      }
    },
    include: {
      role: {
        where: {
          NOT: {
            role: 'USER'
          },
        },
         orderBy: {
          role: 'asc'
        }
      },
    },
    
  });

  return users;
}

async function getUsers(page: number, perPage: number) {
  const skip = (page - 1) * perPage;
  const take = perPage;

  const users = await prisma.user.findMany({
    skip: skip,
    take: perPage,
    where: {
      role: {
        some: {
          role: {
            in: ["ADMIN", "UDVIKLER"]
          }
        }
      }
    },
    include: {
      role: {
        where: {
          NOT: {
            role: 'USER'
          },
        },
         orderBy: {
          role: 'asc'
        }
      },
    },
    
  });

  return users;
}

try this

BørgeOP•2y ago

Nothing changed. Won't that only filter in the role array?

Neto•2y ago

i made a mistake orderBy should be inside the role thing

BørgeOP•2y ago

the first role? inside the where

Neto•2y ago

include: {
      role: {
        where: {
          NOT: {
            role: 'USER'
          },
        },
         orderBy: {
          role: 'asc'
        }
      },
    },

include: {
      role: {
        where: {
          NOT: {
            role: 'USER'
          },
        },
         orderBy: {
          role: 'asc'
        }
      },
    },

BørgeOP•2y ago

Is that not the same thing as before or...?

Neto•2y ago

no before i did the orderBy outside the role and edited the code after

BørgeOP•2y ago

Nothing really changed Idk if im blind, but it still looks like it's inside the role

Rhys•2y ago

Slightly off topic but why are you storing roles as a string and not a number / bitfield? @Børge

Rhys•2y ago

Or enums https://www.prisma.io/docs/concepts/components/prisma-schema/data-model

Prisma

Data model (Reference)

Learn about the concepts for building your data model with Prisma: Models, scalar types, enums, attributes, functions, IDs, default values and more.

BørgeOP•2y ago

Just thought that would be easy. Would it be better storing it in a number or enum?

Rhys•2y ago

From an efficiency perspective, you'd want to store it as a number, instead of having Roles[], you'd just have a number, and each bit of that number represents a different role: i.e: bits: 0 - User 1 - Admin 2 - Manager For evaluating that, you then just set an individual bit i.e 5 decimal becomes 101 in binary, that then is a user with the roles User and Admin 1 in decimal becomes 001 in binary which becomes the roles just User

Rhys•2y ago

Implementation wise, enum array would be easier but if you want the bitfield approach, here's how ive got it implemented in my codebase https://github.com/AnswerOverflow/AnswerOverflow/blob/main/packages/db/src/channel.ts

GitHub

AnswerOverflow/channel.ts at main · AnswerOverflow/AnswerOverflow

Indexing Discord Help Channel Questions into Google - AnswerOverflow/channel.ts at main · AnswerOverflow/AnswerOverflow

BørgeOP•2y ago

I don't fully understand. Could you give an example? If I wanted to set the user just as a Manager What number would that be?

Rhys•2y ago

its not so much what number would it be as it is what bit it would be, you need to set the third bit of a number to 1 to make the user a manager

Rhys•2y ago

https://www.codeproject.com/Articles/2247/An-introduction-to-bitwise-operators

CodeProject

An Introduction to Bitwise Operators

This article gives a brief overview of C style bitwise operators

Rhys•2y ago

https://discordapi.com/permissions.html Discord also uses bitfields for their permissions, check out this website how the number changes as you select more permissions

Discord Permissions Calculator

A small calculator that generates Discord OAuth invite links

Gaming

Programming

How would I order by the role?