https://www.prisma.io/ logo
Join Slack
Powered by
Hey peeps! Started using Prisma and really enjoyin...
# orm-help
p
Hey peeps! Started using Prisma and really enjoying it so far! I have a question regarding optional columns: Let’s say I want to make a query for a team. A team always have a player1 but player2 is optional. How do I query this in prisma? This code will break if only player1 is present on a team. Thanks and yes, I am a big noob 😄
Untitled.js
r
@per 👋 This won’t break. 
player2
if optional will return 
null
if not present.
p
Hey Ryan. Hm, I may have set something wrong up then. When I create a row in my 
team
table, 
player2
is optional but as soon as I use this query with a team that doesnt have a 
player2
I get an error. And thank you so much for replying 👋
r
Can you share your schema?
p
Sure! Maybe I’m handling this the wrong way. Basically what I want in my small (for fun) app is: • Player table with all player info • Team table which connects on or two players • Group (bracket) table which connects all teams in that group
Untitled.js
let me know if it makes sense 🙂
is a GroupMemberships table even needed or should I put the teamrelation in the 
group
table?
r
Just tested it out and works fine 🙂
is a GroupMemberships table even needed or should I put the teamrelation in the 
group
 table?
Do you need to add extra fields about the membership? If so, then let the explicit model be as it is. If not, then you can directly add 
Group
to 
Team
.
p
But can you do my query to fetch the data, if player2 is null?
r
Yes I did the same. What’s the error you’re getting?
p
it’s just my api call that fails and it doesn’t as long as I dont request something that doesnt exist
I’ll try to look a bit further into it and see what comes up. Let’s say I’ll drop the GroupMemberships-table. How can I provide the relation between a group and teams - I seem to do something wrong when setting it up
A team can be in 0 or more groups and a group can have many teams (but only one of each team id)
r
it’s just my api call that fails and it doesn’t as long as I dont request something that doesnt exist (edited)
Can you log the error in the 
catch
block of your API and share it here? The issue might be something else.
Yes the above schema works. Using a plural would be better like 
groups
and 
teams
as this is a many-to-many relation.
p
gotcha!
r
You can read more about relations here.
p
thanks - already read a lot (im just a bit new still ;))
👍 1
let me try some thing s and get back to you. Thank you so much Ryan!
🙌 1
r
Awesome! Let me know how it goes 🙂
p
I just tried deleting my groupMemberships-table and just connect teams to a group, but now I get a different error - i guess my setup is wrong. Used the schema I pasted above
this is how it looks in prisma studio:
so I guess I make a row for each group and then attach teams
but the teamId will stay null (because there are many teams connected)
r
You don’t need 
teamId
. That can be removed.
p
aha, so I only have a reference and no 
fields
teams Team[] @relation(_references_: [id])
r
The 
@relation
is also optional and can be removed 🙂
p
oh
r
Your final schema would look like:
Copy code
model Player {
  id         String   @id @default(cuid())
  name       String
  email      String?  @unique
  profilePic String?
  createdAt  DateTime @default(now()) @map(name: "created_at")
  updatedAt  DateTime @updatedAt @map(name: "updated_at")
  player1    Team[]   @relation("player1")
  player2    Team[]   @relation("player2")
}

model Team {
  id               String     @id @default(cuid())
  player1Id        String
  player2Id        String?
  groupId          String
  player1          Player     @relation("player1", references: [id], fields: [player1Id])
  player2          Player?    @relation("player2", references: [id], fields: [player2Id])
  groups            Group[]
}

model Group {
  id              String            @id @default(cuid())
  name            String
  teams            Team[] 
}
p
gotcha - I removed the groupId in Team as well
ok, so lets say I want to make a query now (with this new schema you posted). I want all teams from a single group
I thought it would be something like:
Copy code
const teamsTest = await prisma.group.findMany({
    where: { id: context.query.id as string },
    include: { teams: true },
  })
(context.query.id is from nextJs)
oh it is - restart of dev server made it work 🙂
making this change in relations and tables made the query to the optional 
player2
work
💯 1
Ryan - you’re the best. Thanks a bunch. If you have a link to where I can gift you a coffee, let me know
🙌 1
(and one last thing - sorry!): can I filter the extra id from the result
I only need the 
player1
and 
player2
where the id is nested. I dont need player1id explicit
r
Using 
select
with only the necessary fields should work here.
p
yup! just fixed it before you wrote!
let me know if I can buy you a coffee! have a great day 😎
💯 1
yes?
r
Another helpful point: Using 
ctrl+space
inside the arguments will let you know all the options that you can pass.
p
yeah, I figured. Thanks a bunch
👍 1
@Ryan (and just quickly - I thought @relation was needed when you reference other tables. How come I can just omit that?)
r
For many-to-many relations, it’s not required as it is implied.
p
makes sense - thanks
👍 1
5 Views
Open in Slack
PreviousNext
Powered by