CODEWITHSUNDEEP
javascriptmysqlnode.jsprisma
timintas
timintas

Reputation: 61

Prisma: update nested entities in a single query

I am using Prisma and Express.js to make requests to my MySQL database table.

I have one-to-many relationship between my Contest-Round tables and am trying to write a query which would allow me to differently update rounds for a given contest. Given the schema:

model Contest {
    id            Int      @id @default(autoincrement())
    name          String   @unique
    rounds        Round[]
    ... other fields
}

model Round {
    id        Int       @id @default(autoincrement())
    name      String
    contestId Int
    contest   Contest   @relation(fields: [contestId], references: [id], onDelete: Cascade)
    ... other fields
}

What I want to achieve is to update the contest from

{
    id: 100,
    name: 'contest1',
    rounds: [
        {
             id: 1,
             name: 'round1',
             contestId: 100,
        },
        {
             id: 2,
             name: 'round2',
             contestId: 100,
        }
    ]

to for example

{
    id: 100,
    name: 'contest1',
    rounds: [
        {
             id: 1,
             name: 'round1Updated',
             contestId: 100,
        },
        {
             id: 2,
             name: 'round2UpdatedDifferently',
             contestId: 100,
        }
    ]

where the round names I get from the form value from HTML.

I haven't found any examples on updating different nested entities, so I'm expecting it to be something like this:

     var updated = await prisma.contest.update({
            where: {
                id: 100
            },
            data: {
                name: data.name,
                rounds: {
                    update: {
                        where: {
                            id: { in: [1, 2] },
                        },
                        data: {
                            name: ['round1Updated', 'round2UpdatedDifferently']   
                        },
                    },
                }
            },
            include: {
                rounds: true,
            }
        });

Any ideas or clues would be appreciated.

Upvotes: 4

Views: 18465

Answers (3)

timintas
timintas

Reputation: 61

So the initial requirements changed a bit and I only needed to add/remove rounds depending on the form data (not update as state in the question). I ended up with getting the contest first and then rounds that should be added/removed:

// get the target contest first
contes contest = await prisma.contest.findFirst({
  where: { id },
  select: {
    rounds: true,
  }
});

// newly created round on my form
const roundsToBeCreated = // rounds that the existing contest doesn't have yet, but the form does

// deleted rounds on my form
const roundsToBeDeleted = // rounds that the existing contest has, but form data doesn't

const updated = await prisma.contest.update({ where: { id },
        data: {
            rounds: {
                deleteMany: {
                    id: {
                        in: roundsToBeDeleted.map(r => r.id)
                    },
                },
                createMany: {
                    data: roundsToBeCreated
                },
            }
        }
    });

For changing Rounds' fields I believe this query is not suitable, since I also have other entities that depend on Rounds. And for changing e.g the Round name only I would lose their recrords completely. So for that purpose I think I need a separate query for updating the Round entries.

Upvotes: 0

Osman Goni Sufy
Osman Goni Sufy

Reputation: 21

const updated = await prisma.contest.update({
  where: {id: data.id},
  data: {
    name: data.name,
    rounds: {
      deleteMany: {},
      createMany: {data: rounds},
    }
  },
  include: {rounds: true},
});

Upvotes: 1

Ironolife
Ironolife

Reputation: 966

Will something like this work?

const updated = await prisma.contest.update({
  where: { id: 100 },
  data: {
    name: data.name,
    rounds: {
      deleteMany: { id: { in: [1, 2] } }, // Delete existing records first
      createMany: { // Update by creating new records
        data: [
          { id: 1, name: "round1Updated" },
          { id: 2, name: "round2UpdatedDifferently" },
        ]
      }
    }
  },
  include: { rounds: true }
});

From form data

const updated = await prisma.contest.update({
  where: { id: data.id },
  data: {
    name: data.name,
    rounds: {
      deleteMany: { id: { in: data.rounds.map(({ id }) => id) } },
      createMany: { data: rounds }
    }
  },
  include: { rounds: true }
});

Note that the ordering of operations do matter:
https://github.com/prisma/prisma/discussions/6263

Upvotes: 4

Related Questions