Theo's Typesafe CultBørge

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'
          },
        }
      }
    }
  });

Neto•378d ago

can you show the error?

Børge•378d 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•378d ago

try this

orderBy: {
      role: 'asc'
    }

orderBy: {
      role: 'asc'
    }

Børge•378d 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•378d ago

can you show the whole query?

Børge•378d 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•378d ago

yeah

Børge•378d ago

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

Neto•378d ago

i have this setup for orderBy

Neto•378d ago

and worked fine

Børge•378d ago

What comes first then? Staff or Partner?

Neto•378d ago

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

Børge•378d 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•378d 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ørge•378d ago

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

Neto•378d ago

i made a mistake orderBy should be inside the role thing

Børge•378d ago

the first role? inside the where

Neto•378d ago

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

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

Børge•378d ago

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

Neto•378d ago

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

Børge•378d ago

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

Rhys•378d ago

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

Rhys•378d 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ørge•378d ago

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

Rhys•378d 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•378d 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ørge•378d 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•378d 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•378d 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•378d 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

Theo's Typesafe Cult Join

21KMembers

View on Discord

Want results from more Discord servers?

Add your server

Prisma Create with Connect IssueHey! I wasn't able to find another thread explaining this, but I'd love help with this! I have two Generating image background jobI'm making a game where you can create levels in HTML5 Canvas and upload them to the api. I will nee Help with Vercel deploymentHi folks, I am having issues deploying to vercel. I keep getting this error:My DB shouldn’t be this slow, RIGHT❓A fresh DB (pscale) and Table with just two entries, but takes 10s to fetch them (prisma studio) I’❌ tRPC failed on profile.getUserByUsername: No "query"-procedure on path "profile.getUserByUsername"I am a bit lost. This is the profileRouter. export const profileRouter = createTRPCRouter({ getU Help with Prisma Validator static evaluationI am trying to build a helper function that will allow me to abstract away some deeply nested includ Does invalidate(); perform an actual refetch?I am half way into the course video and I see `void ctx.post.getAll.invalidate();` to show the new p SWR - array of data in one request, one item from array in anotherSo I am using SWR in my nextJS app and I have two routes GET `/courses` gets all classes you are en Clerk re-render parent server component on sign out with custom sign out button.Hi I'm probably being a little stupid here but I'm just wondering what the best and most simple way Prisma schema questionHow can I efficiently maintain a user's balance in a database given the following prisma schema, whe T3 Firebase solution? (SMS OTP Auth)I know I know, we love Auth.js and Clerk. But there's a problem when it comes to SMS authentication.How do I make Jotai work with trpc on the t3 stack?I am having trouble finding ways to work with Jotai and trpc on nextjs.Any recommend open source react project?I am trying to optimize the project of company. It is like a CRUD based shopping web. I do use tanst Rendering a blob in NextjsI am having difficulties rendering an image which is restored in form as link and when i console.log What's the difference between React.ComponentProps and React.PropsWithChildren ?I just saw someone post something on youtube but didnt't really explain it. Whats the difference be nextra with tailwindcssthrough the uploadthings docs i found out about nextra ... i would like to use tailwind but i cant g How to use next-auth/clerk.js for getting user session```ts /** server/uploadthing.ts */ import { createFilething, type FileRouter } from "uploadthing/ser Error with mdx file webpack parserI am trying to integrate an MDX files that I want blog articles in. I have a component <ArticleLayou How to debug Vercel cache misses with tRPC?Basically. I have a tRPC endpoint with the following NextApiHandler config: ```ts export default cr How do you run a one-off prisma script?Hello 👋 I'm trying to run a little script to change some stuff in my database. Kind of like a `pris

Gaming

Programming

How would I order by the role?