Failing to create relationship logic between two database tables using Prisma and Javascript

Question

This is, as far as I can tell, a logic problem that I just can't wrap my head around. Using next-connect I have a function for updating an entry in the database:

  .put(async (req, res) => {
    const data = req.body;
    const { dob, roles, cases } = data ?? {};
    const convertedDob = dob ? new Date(dob) : null;

    const roleIds = roles?.map((role: Role) => {
      return { id: role.id };
    });

    const caseIds = cases?.map((_case: Case) => {
      return { id: _case.id };
    });

    data.dob = convertedDob;
    delete data.roles;
    delete data.formData;

    const user = await getUserDataFromSession(req, res);
    throwIfNoCurrentTeam(user?.currentTeam);

    try {
      const person: Person = await prisma.person.update({
        where: { id: data.id },
        data: {
          ...data,
          roles: { connect: roleIds },
          cases: { connect: caseIds },
        },
      });

      if (person && person.linkedIds) {
        // get linkedId arrays of all people linked to this person
        const associatedPeopleIdArrays = await prisma.$transaction(
          person.linkedIds.map((id) =>
            prisma.person.findUnique({
              where: { id },
              select: { id: true, linkedIds: true },
            }),
          ),
        );

        const backLinkArray = associatedPeopleIdArrays.map((linkedPerson) => {
          const linkedPersonIds = linkedPerson?.linkedIds;
          const linkedPersonOwnId = linkedPerson?.id;

          // if the array of linkedIds already includes the person we are back-linking, enter next check
          if (linkedPersonIds?.includes(person.id)) {
            // if they both include each other, do nothing
            if (person.linkedIds.includes(linkedPersonOwnId as string)) {
              return { id: linkedPersonOwnId, linkedIds: linkedPersonIds };
            }
            // if linkedPersonIds includes person.id but person.linkedIds does not include linkedPersonOwnId, remove the relationship
            return { id: linkedPersonOwnId, linkedIds: linkedPersonIds.filter((id) => id !== person.id) };
            // else add the current person's id to each person's array that we are back-linking
          } else {
            return {
              id: linkedPersonOwnId,
              linkedIds: linkedPersonIds ? [...linkedPersonIds, person.id] : [person.id],
            };
          }
        });

        // write the new linkedId array to each associated person
        await prisma.$transaction(
          backLinkArray.map((linkedPerson) =>
            prisma.person.update({
              where: { id: linkedPerson?.id },
              data: {
                linkedIds: linkedPerson?.linkedIds,
              },
            }),
          ),
        );
      }

      return res.status(200).json({ updated: person });
    } catch (err: any) {
      console.log({ err });
      handleApiError('Failed to update person', 500, err);
    }
  });

I've left comments to try and follow what's happening which I have left in in case they help to understand what I'm trying to do. I've created logs all over the place to see what's happening, but the issue really is in the if/else logic.

The function works correctly to establish a relationship between two people. If an id is added to a person's linkedIds array, then that person's id is also added to the array of the associated person.

But I need additional logic to remove a relationship if an id is removed from a person's linkedIds, and this is where it's breaking down. Currently the logic seems to correctly enter this if statement: if (linkedPersonIds?.includes(person.id)), but then it always enters the following if statement: if (person.linkedIds.includes(linkedPersonOwnId as string)).

I would expect that if a person was removed from the linkedIds array then this statement would return false, but it doesn't. It returns true every time, so the relationship never gets deleted. What is going wrong? What have I missed?

Sorry it's such a mess, I hope you're able to follow this madness!

score 0 · Accepted Answer · answered Apr 08 '23 at 09:42

I can see now what the crux of the issue is... when creating associatedPeopleArrays it won't add the recently deleted id to the backLinkArray which means that there will never be a check for that id!

So what I needed to do was get the previous version of the linkedIds array before it was updated which meant making a change on the frontend to make sure that that was sent through along with the updated data.

That way I could make a comparison between the old linkedIds and the new linkedIds to find the difference. My code is verbose and long-winded I'm sure, but this is my solution and it works!

      if (person?.linkedIds || initialLinkedIds) {

        // ADD RELATIONSHIPS
        // get linkedId arrays of all people newly linked to this person
        const newlyAssociatedPeopleIdArrays = await prisma.$transaction(
          person.linkedIds.map((id: string) =>
            prisma.person.findUnique({
              where: { id },
              select: { id: true, linkedIds: true },
            }),
          ),
        );

        // populate the associated peoples' arrays with the person that just added them
        const addRelationshipArray = newlyAssociatedPeopleIdArrays?.map((linkedPerson) => {
          const linkedPersonIds = linkedPerson?.linkedIds;
          const linkedPersonOwnId = linkedPerson?.id;

          if (!linkedPersonIds?.includes(person.id)) {
            return { id: linkedPersonOwnId, linkedIds: [...linkedPersonIds, person.id] };
          }

          return linkedPerson;
        });

        // REMOVE RELATIONSHIPS
        // get the difference between the old linkedIds and the current linkedIds
        const removedIds = initialLinkedIds.filter((id: string) => !person.linkedIds.includes(id));

        // get the linkedId arrays for each id that has been removed
        const newlyRemovedPeopleIdArrays = await prisma.$transaction(
          removedIds.map((id: string) =>
            prisma.person.findUnique({
              where: { id },
              select: { id: true, linkedIds: true },
            }),
          ),
        );

        // map over those linkedId arrays and remove the current person
        const removeRelationshipArray = newlyRemovedPeopleIdArrays.map((unlinkedPerson) => ({
          ...unlinkedPerson,
          linkedIds: unlinkedPerson.linkedIds.filter((id: string) => id !== person.id),
        }));

        // combine the two relationship arrays
        const combinedUpdateArray = [...removeRelationshipArray, ...addRelationshipArray];

        // write these new arrays to the database
        await prisma.$transaction(
          combinedUpdateArray.map((linkedPerson) =>
            prisma.person.update({
              where: { id: linkedPerson?.id },
              data: {
                linkedIds: linkedPerson?.linkedIds,
              },
            }),
          ),
        );
      }

Failing to create relationship logic between two database tables using Prisma and Javascript

1 Answers1