https://www.prisma.io/ logo
Join Slack
Powered by
Is there a better way to do this in a single query...
# orm-help
h
Is there a better way to do this in a single query?
Copy code
const match = await prisma.match.findUnique({
        where: {
            session_id: 1234
        }
    });

    const arbitrated = await prisma.arbitrated.create({
        data: {
            match: {
                connect: {
                    id: match.id
                }
            },
            arbitrated_session_id: 5678
        }
    });
r
Yes:
Copy code
await prisma.match.update({
    where: { session_id: 1234 },
    data: {
      arbitrated: { create: { arbitrated_session_id: 5678 } },
    },
  })
Your schema would need to have an optional relation for this to work:
Copy code
model match {
  id         Int         @id @default(autoincrement())
  session_id BigInt      @unique
  arbitrated arbitrated?
}
model arbitrated {
  id                    Int    @id @default(autoincrement())
  match                 match? @relation(fields: [matchId], references: [id])
  matchId               Int?
  arbitrated_session_id BigInt @unique
}
The reason being tat as this is a 1-1 relation, you are connecting a new 
arbitrated
to 
match
. But the old 
arbitrated
that was connected will not have a 
match
anymore. This would cause a constraint error as the relation is required. Which is why you need to keep the relation optional.
h
@Ryan Hmm, but there should never be an arbitrated entry without a match relation.
But could be a match without a arbitrated relation because arbitrated is something linked to a match only in some cases.
Also, what would happen if the given match already have a relation? I want to only add arbitrated if it doesn't exist, otherwise it should return the arbitrated that already exists.
r
Yes so you cannot connect a new 
arbitrated
to a 
match
because the old 
arbitrated
which was already connected will not have a match anymore.
This is bound to happen due to the relation being required.
h
even if the 1-to-1 relation is optional?
Just to be clear, so if updating a match that already has an arbitrated relation the update will fail just return the existing entry? 🙂
r
even if the 1-to-1 relation is optional?
No then it will work perfectly as I’d sent the schema above.
Just to be clear, so if updating a match that already has an arbitrated relation the update will fail just return the existing entry?
If the relation is required, then yes it will fail.
h
thx, now i'm getting "Null constraint violation on the fields: (
matchId
)" when trying to use that update() query.
and if it fails, how would i get the read the data, with another findUnique()?
r
thx, now i’m getting “Null constraint violation on the fields: (
matchId
)” when trying to use that update() query.
On optional relations?
h
yeah
let me completly wipe the db, maybe migrations failed.
r
Migrations shouldn’t fail though as you’re converting a required to an optional. Was there an error in the terminal when running migrate?
and if it fails, how would i get the read the data, with another findUnique()?
It will never fail for optional relations 🙂
h
when an entry exists in match i get: Unique constraint failed on the fields: (
session_id
)
but really i would like to have the conflicting entry returned.
r
What’s the query you’re making?
h
Seems this could be the way to go?
Copy code
const match = await prisma.match.update({
            where: {
                session_id: matchId
            },
            data: {
                arbitrated: {
                    create: {
                        session_id: Math.floor(Math.random() * 100)
                    }
                }
            },
            include: {
                arbitrated: true
            }
        });
nevermind that math.random just to test with different ids and not making conflicts 🙂
problem is that it is creating a new arbitrated entry for the very same match with matchId as null.
r
create
will always set the 
matchId
to the one that you’re updating with. I could send you an example.
h
but it doesn't
only the latest one
so it is inserting one arbitrated per update() query on match
with only the latest one having matchId as non null.
r
Yes only 1 will have it because it’s a 1-1 relation. If you need multiple 
arbitrated
to be associated with a single 
match
, then you need a 1-many relation.
h
yeah, but i do not want to add an arbitrated if one already exists for that particular match.
So create arbitrated for match if arbitrated relation does not already exists. And return the result.
This is maybe a little hacky, but this seems to work:
Copy code
const match = await prisma.match.update({
            where: {
                session_id: matchId
            },
            data: {
                arbitrated: {
                    upsert: {
                        create: {
                            session_id: Math.floor(Math.random() * 100)
                        },
                        update: {}
                    }
                }
            },
            include: {
                arbitrated: true
            }
        });
đź’Ż 1
upsert making sure we are only creating new entries in the arbitrated table and not updating it if alreay exists.
đź’Ż 1
thx for all help 🙂
Really appriciate it 🙂
🙌 1
Open in Slack
PreviousNext
Powered by