Error when Creating User-Group Relation Due to Foreign Key Constraint Violation in Join Field Setup

[BM-Buschmann]

I currently have some issues with the join field functionality.

I have a situation where I have a user that has a join field to an intermediate collection, "UserGroupRelations", which has a relation to both the "users" collection and the "groups" collection. The "groups" collection also has a join field to the "UserGroupRelations" collection.

Now, the issue is that when creating a new user, I trigger a hook that first checks if a default group document exists. If it doesn't, I create the default group document and then create the document in the "UserGroupRelations" collection. The problem arises when I try to create the "UserGroupRelations" document. I get the error:

[15:27:32] ERROR: insert or update on table "user_group_relations" violates foreign key constraint "user_group_relations_user_id_users_id_fk"
      "length": 317,
      "name": "error",
      "severity": "ERROR",
      "code": "23503",
      "detail": "Key (user_id)=(17) is not present in table \"users\".",
      "schema": "public",
      "table": "user_group_relations",
      "constraint": "user_group_relations_user_id_users_id_fk",
      "file": "ri_triggers.c",
      "line": "2599",
      "routine": "ri_ReportViolation"

I guess this is due to the fact that I am trying to create a relation to a document that has a join to the relation document I am trying to create. Is that not possible? Am I approaching this the wrong way? Any tips are welcome.

---

1. The users collection

import { CollectionConfig } from 'payload'

export const Users: CollectionConfig = {
  slug: 'users',
  admin: {
    useAsTitle: 'firstName',
    defaultColumns: ['firstName', 'lastName', 'email'],
  },
  auth: { disableLocalStrategy: true },
  fields: [
    {
      name: 'email',
      type: 'email',
      label: 'E-Mail',
      required: true,
    },
    {
      name: 'firstName',
      type: 'text',
      label: 'First Name',
    },
    {
      name: 'lastName',
      type: 'text',
      label: 'Last Name',
    },
    {
      name: 'groups',
      type: 'join',
      collection: 'user-group-relations',
      on: 'user',
      hasMany: true,
      defaultSort: 'group',
      admin: {
        position: 'sidebar',
        defaultColumns: ['group', 'role', 'joinDate'],
      },
      hooks: {
        afterChange: [
          async ({ req, originalDoc }) => {
            const { payload } = req

            // Fetch user-group relations for the current user
            const relations = await payload.find({
              collection: 'user-group-relations',
              where: {
                user: {
                  equals: originalDoc?.id,
                },
              },
            })

            // Map relations to their corresponding group IDs
            return relations.docs.map((rel) => rel.group)
          },
        ],
      },
    },
    {
      name: 'userName',
      type: 'text',
      label: 'Username',
    },
    {
      name: 'userImageUrl',
      type: 'text',
      hidden: true,
    },
  ],
  hooks: {
    afterOperation: [
      async ({ req, result, operation }) => {
        if (operation === 'create') {
          const { payload } = req

          // Step 1: Check if a default group exists
          let defaultGroup = await payload.findByID({
            collection: 'groups',
            id: 1,
          })

          if (!defaultGroup) {
            // Step 2: If no default group exists, create one
            const createdGroup = await payload.create({
              collection: 'groups',
              data: {
                name: 'Default Group', // The name of your default group
              },
            })

            // Step 3: Update defaultGroup to use the newly created group
            defaultGroup = createdGroup // This ensures you're accessing the group itself
          }

          // Step 3: Create user-group relation if it doesn't exist
          const userGroupRelation = await payload.find({
            collection: 'user-group-relations',
            where: {
              user: {
                equals: result.id,
              },
              group: {
                equals: defaultGroup.id,
              },
            },
          })

          // Step 4: Create user-group relation if it doesn't exist
          if (!userGroupRelation.docs.length) {
            await payload.create({
              collection: 'user-group-relations',
              data: {
                user: result.id,
                group: defaultGroup.id,
                role: 'member', // Set the user's role in the group
                joinDate: new Date().toISOString(), // Add the joinDate field
              },
            })
          }
          return result
        }
      },
    ],
  },
}

2. The user-group-relations collection

import { CollectionConfig } from 'payload'

export const UserGroupRelations: CollectionConfig = {
  slug: 'user-group-relations',
  fields: [
    {
      name: 'user',
      type: 'relationship',
      relationTo: 'users',
      required: true,
    },
    {
      name: 'group',
      type: 'relationship',
      relationTo: 'groups',
      required: true,
    },
    {
      name: 'role',
      type: 'select',
      options: [
        { label: 'Admin', value: 'admin' },
        { label: 'Manager', value: 'manager' },
        { label: 'Member', value: 'member' },
      ],
      defaultValue: 'member',
      required: true,
    },
    {
      name: 'joinDate',
      type: 'date',
      required: true,
      defaultValue: () => new Date().toISOString(),
    },
  ],
}

3. and lastly the groups collection

import { CollectionConfig } from 'payload'

export const Groups: CollectionConfig = {
  slug: 'groups',
  admin: {
    useAsTitle: 'name',
    defaultColumns: ['name', 'adminUser'],
  },
  fields: [
    {
      name: 'name',
      type: 'text',
      label: 'Group Name',
      required: true,
    },
    {
      name: 'parentGroup',
      type: 'relationship',
      relationTo: 'groups',
      label: 'Parent Group',
      required: false,
    },
    {
      name: 'users',
      type: 'join',
      collection: 'user-group-relations',
      on: 'group',
      hasMany: true,
      defaultSort: 'user',
      admin: {
        position: 'sidebar',
        defaultColumns: ['user', 'role', 'joinDate'],
      },
      hooks: {
        afterChange: [
          async ({ req, originalDoc }) => {
            const { payload } = req

            // Fetch user-group relations for the current user
            const relations = await payload.find({
              collection: 'user-group-relations',
              where: {
                group: {
                  equals: originalDoc.id,
                },
              },
            })

            // Map relations to their corresponding group IDs
            return relations.docs.map((rel) => rel.group)
          },
        ],
      },
    },
    {
      name: 'associatedCollections',
      type: 'array',
      labels: {
        singular: 'Associated Collection',
        plural: 'Associated Collections',
      },
      fields: [
        {
          name: 'collectionName',
          type: 'text',
          label: 'Collection Name',
          required: true,
        },
      ],
    },
  ],
  hooks: {},
}