payload icon indicating copy to clipboard operation
payload copied to clipboard
verified publisher icon payloadcms

Field "id" is invalid when updating search records

Open TimLanzi opened this issue 1 year ago • 3 comments

Describe the Bug

In experimenting with the website template for 3.0, I found that the following code in /src/search/beforeSync.ts doesn't work as expected:


  if (categories && Array.isArray(categories) && categories.length > 0) {
    // get full categories and keep a flattened copy of their most important properties
    try {
      const mappedCategories = categories.map((category) => {
        const { id, title } = category

        return {
          relationTo: 'categories',
          id,
          title,
        }
      })

      modifiedDoc.categories = mappedCategories
    } catch (err) {
      console.error(
        `Failed. Category not found when syncing collection '${collection}' with id: '${id}' to search.`,
      )
    }
  }

After updating a document, the search result categories will look like this:

Image

I'm using postgres for my database, and the id looks like a mongo id. Plus, the title isn't saved. Logging the categories from the originalDoc yields an array of numbers (i.e. [3, 1]). So I figured I'd need to brute force the titles and correct ids into the record. So I updated the code as follows:

  if (categories && Array.isArray(categories) && categories.length > 0) {
    // get full categories and keep a flattened copy of their most important properties
    try {
      const populatedCategories = await payload.find({
        collection: "categories",
        where: { id: { in: categories } },
        pagination: false,
      });

      const mappedCategories = populatedCategories.docs.map((category) => {
        const { id, title } = category

        return {
          relationTo: 'categories',
          id,
          title,
        }
      })

      modifiedDoc.categories = mappedCategories
    } catch (err) {
      console.error(
        `Failed. Category not found when syncing collection '${collection}' with id: '${id}' to search.`,
      )
    }
  }

While not ideal, it did the job. I couldn't find a better way in the docs. However, this came with a new issue. If 2 posts use the same category, I get the following error:


[13:11:02] ERROR: Error updating search document.
    err: {
      "type": "ValidationError",
      "message": "The following field is invalid: id",
      "stack":
          ValidationError: The following field is invalid: id
              at upsertRow (webpack-internal:///(rsc)/./node_modules/@payloadcms/drizzle/dist/upsertRow/index.js:345:19)
              at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
              at async Object.updateOne (webpack-internal:///(rsc)/./node_modules/@payloadcms/drizzle/dist/update.js:49:20)
              at async updateByIDOperation (webpack-internal:///(rsc)/./node_modules/payload/dist/collections/operations/updateByID.js:279:22)
              at async syncWithSearch (webpack-internal:///(rsc)/./node_modules/@payloadcms/plugin-search/dist/Search/hooks/syncWithSearch.js:108:29)
              at async hooks.afterChange (webpack-internal:///(rsc)/./node_modules/@payloadcms/plugin-search/dist/index.js:37:37)
              at async eval (webpack-internal:///(rsc)/./node_modules/payload/dist/collections/operations/updateByID.js:353:22)
              at async updateByIDOperation (webpack-internal:///(rsc)/./node_modules/payload/dist/collections/operations/updateByID.js:351:9)
              at async Object.updateByID (webpack-internal:///(rsc)/./node_modules/@payloadcms/next/dist/routes/rest/collections/updateByID.js:37:15)
              at async eval (webpack-internal:///(rsc)/./node_modules/@payloadcms/next/dist/routes/rest/index.js:760:19)
              at async AppRouteRouteModule.do (/home/tlanzi/Projects/Test/search-delete-bug-postgres/node_modules/next/dist/compiled/next-server/app-route.runtime.dev.js:10:33313)
              at async AppRouteRouteModule.handle (/home/tlanzi/Projects/Test/search-delete-bug-postgres/node_modules/next/dist/compiled/next-server/app-route.runtime.dev.js:10:40382)
              at async doRender (/home/tlanzi/Projects/Test/search-delete-bug-postgres/node_modules/next/dist/server/base-server.js:1455:42)
              at async responseGenerator (/home/tlanzi/Projects/Test/search-delete-bug-postgres/node_modules/next/dist/server/base-server.js:1814:28)
              at async DevServer.renderToResponseWithComponentsImpl (/home/tlanzi/Projects/Test/search-delete-bug-postgres/node_modules/next/dist/server/base-server.js:1824:28)
              at async DevServer.renderPageComponent (/home/tlanzi/Projects/Test/search-delete-bug-postgres/node_modules/next/dist/server/base-server.js:2240:24)
              at async DevServer.renderToResponseImpl (/home/tlanzi/Projects/Test/search-delete-bug-postgres/node_modules/next/dist/server/base-server.js:2278:32)
              at async DevServer.pipeImpl (/home/tlanzi/Projects/Test/search-delete-bug-postgres/node_modules/next/dist/server/base-server.js:960:25)
              at async NextNodeServer.handleCatchallRenderRequest (/home/tlanzi/Projects/Test/search-delete-bug-postgres/node_modules/next/dist/server/next-server.js:281:17)
              at async DevServer.handleRequestImpl (/home/tlanzi/Projects/Test/search-delete-bug-postgres/node_modules/next/dist/server/base-server.js:853:17)
              at async /home/tlanzi/Projects/Test/search-delete-bug-postgres/node_modules/next/dist/server/dev/next-dev-server.js:373:20
              at async Span.traceAsyncFn (/home/tlanzi/Projects/Test/search-delete-bug-postgres/node_modules/next/dist/trace/trace.js:153:20)
              at async DevServer.handleRequest (/home/tlanzi/Projects/Test/search-delete-bug-postgres/node_modules/next/dist/server/dev/next-dev-server.js:370:24)
              at async invokeRender (/home/tlanzi/Projects/Test/search-delete-bug-postgres/node_modules/next/dist/server/lib/router-server.js:183:21)
              at async handleRequest (/home/tlanzi/Projects/Test/search-delete-bug-postgres/node_modules/next/dist/server/lib/router-server.js:360:24)
              at async requestHandlerImpl (/home/tlanzi/Projects/Test/search-delete-bug-postgres/node_modules/next/dist/server/lib/router-server.js:384:13)
              at async Server.requestListener (/home/tlanzi/Projects/Test/search-delete-bug-postgres/node_modules/next/dist/server/lib/start-server.js:142:13)
      "data": {
        "id": 4,
        "errors": [
          {
            "message": "Value must be unique",
            "path": "id"
          }
        ]
      },
      "isOperational": true,
      "isPublic": false,
      "status": 400,
      "name": "ValidationError"
    }

I've tried this in a fresh instance of the website template using postgres as well as another project where I have a "Resource" collection with "Category", "Tag", and "Source" related collections used in the same way. This bug appears if there is ever any overlap on relations. Sometimes I'll need to add the category, save, make another edit, and save again before the error shows up.

Link to the code that reproduces this issue

https://github.com/TimLanzi/payload-search-relationship-id-bug

Reproduction Steps

  1. Use create-payload-app choose website template and postgres database
  2. Modify /src/search/beforeSync.ts as follows:
  if (categories && Array.isArray(categories) && categories.length > 0) {
    // get full categories and keep a flattened copy of their most important properties
    try {
      const populatedCategories = await payload.find({
        collection: "categories",
        where: { id: { in: categories } },
        pagination: false,
      });

      const mappedCategories = populatedCategories.docs.map((category) => {
        const { id, title } = category

        return {
          relationTo: 'categories',
          id,
          title,
        }
      })

      modifiedDoc.categories = mappedCategories
    } catch (err) {
      console.error(
        `Failed. Category not found when syncing collection '${collection}' with id: '${id}' to search.`,
      )
    }
  }
  1. Create account and seed database
  2. Give post "Dollar and Sense" a category (I chose Technology)
  3. Give post "Global Gaze" the same category. You should see the error. If not, make another arbitrary edit to the document, save, and you should see it now.

Which area(s) are affected? (Select all that apply)

plugin: search

Environment Info

Binaries:
  Node: 22.7.0
  npm: 10.8.2
  Yarn: 1.22.22
  pnpm: 9.8.0
Relevant Packages:
  payload: 3.1.0
  next: 15.0.3
  @payloadcms/db-postgres: 3.1.0
  @payloadcms/email-nodemailer: 3.1.0
  @payloadcms/graphql: 3.1.0
  @payloadcms/live-preview: 3.1.0
  @payloadcms/live-preview-react: 3.1.0
  @payloadcms/next/utilities: 3.1.0
  @payloadcms/payload-cloud: 3.1.0
  @payloadcms/plugin-form-builder: 3.1.0
  @payloadcms/plugin-nested-docs: 3.1.0
  @payloadcms/plugin-redirects: 3.1.0
  @payloadcms/plugin-search: 3.1.0
  @payloadcms/plugin-seo: 3.1.0
  @payloadcms/richtext-lexical: 3.1.0
  @payloadcms/translations: 3.1.0
  @payloadcms/ui/shared: 3.1.0
  react: 19.0.0-rc-65a56d0e-20241020
  react-dom: 19.0.0-rc-65a56d0e-20241020
Operating System:
  Platform: linux
  Arch: x64
  Version: #202405300957~1732141768~22.04~f2697e1 SMP PREEMPT_DYNAMIC Wed N
  Available memory (MB): 31971
  Available CPU cores: 8

TimLanzi avatar Nov 22 '24 18:11 TimLanzi

In my case, I found that when updating a post, the code in /src/search/beforeSync.ts is triggered twice. The first time it is triggered, the categories is already populated like so:

categories: [
    {
      id: 1,
      title: 'Technology',
      updatedAt: '2024-11-25T10:53:19.507Z',
      createdAt: '2024-11-25T10:53:19.507Z'
    },
    {
      id: 2,
      title: 'Science',
      updatedAt: '2024-11-25T10:53:19.507Z',
      createdAt: '2024-11-25T10:53:19.507Z'
    },
    ...
]

And the second time it is triggered with the categories not populated like:

categories: [ 1, 2 ]

And this happens not only to the categories, but anything with relationship.

daohall avatar Nov 27 '24 01:11 daohall

One more finding is that any field with name "id" seems like having special meaning, and will be altered automatically when saved to database. If you change the field name to "_id" instead in src/search/fieldOverrides.ts, the value will be saved correctly.

daohall avatar Nov 27 '24 02:11 daohall

This issue has been marked as stale due to lack of activity.

To keep this issue open, please indicate that it is still relevant in a comment below.

github-actions[bot] avatar Dec 20 '24 05:12 github-actions[bot]

I just ran into this as well, when using the website template. My use case was having multiple posts in the template-provided Posts collection that share a category, as well as having a single post ahve two categories. This was running locally with a Postgres database.

Upong seeding my database with a post that had two categories, the following would be showed upon entering it and the search document getting updated (I added some extra logging in post revalidation). This is two posts that share the same category:


// Custom logging in revalidatePosts.ts:
// payload.logger.info(JSON.stringify(doc.categories))

[08:20:33] INFO: — Seeding posts...
[08:20:33] INFO: Revalidating post at path: /posts/anistropy-high-fidelity
[08:20:33] INFO: [{"id":102,"title":"News","type":"generic","parent":null,"breadcrumbs":[{"id":"67beced1523669d3a2eb3fa4","doc":102,"url":null,"label":"News"}],"updatedAt":"2025-02-26T08:20:33.682Z","createdAt":"2025-02-26T08:20:33.672Z"}]
[08:20:33] INFO: Done revalidating post (81) at path: /posts/anistropy-high-fidelity
[08:20:33] INFO: Revalidating post at path: /posts/global-gaze
[08:20:33] INFO: [{"id":102,"title":"News","type":"generic","parent":null,"breadcrumbs":[{"id":"67beced1523669d3a2eb3fa4","doc":102,"url":null,"label":"News"}],"updatedAt":"2025-02-26T08:20:33.682Z","createdAt":"2025-02-26T08:20:33.672Z"}]
[08:20:33] INFO: Done revalidating post (82) at path: /posts/global-gaze
[08:20:33] ERROR: Error syncing search document related to posts with id: '82'.
    err: {
      "type": "ValidationError",
      "message": "The following field is invalid: id",
      "stack":
          ValidationError: The following field is invalid: id
              at upsertRow (webpack-internal:///(rsc)/./node_modules/@payloadcms/drizzle/dist/upsertRow/index.js:347:19)
              at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
              at async Object.create (webpack-internal:///(rsc)/./node_modules/@payloadcms/drizzle/dist/create.js:15:20)
              at async createOperation (webpack-internal:///(rsc)/./node_modules/payload/dist/collections/operations/create.js:187:19)
              at async syncDocAsSearchIndex (webpack-internal:///(rsc)/./node_modules/@payloadcms/plugin-search/dist/utilities/syncDocAsSearchIndex.js:52:17)
              at async hooks.afterChange (webpack-internal:///(rsc)/./node_modules/@payloadcms/plugin-search/dist/index.js:46:37)
              at async eval (webpack-internal:///(rsc)/./node_modules/payload/dist/collections/operations/create.js:273:22)
              at async createOperation (webpack-internal:///(rsc)/./node_modules/payload/dist/collections/operations/create.js:271:9)
              at async seed (webpack-internal:///(rsc)/./src/endpoints/seed/index.ts:215:24)
              at async POST (webpack-internal:///(rsc)/./src/app/(frontend)/next/seed/route.ts:37:9)
              at async AppRouteRouteModule.do (/home/user/website/node_modules/next/dist/compiled/next-server/app-route.runtime.dev.js:10:32847)
              at async AppRouteRouteModule.handle (/home/user/website/node_modules/next/dist/compiled/next-server/app-route.runtime.dev.js:10:39868)
              at async doRender (/home/user/website/node_modules/next/dist/server/base-server.js:1452:42)
              at async responseGenerator (/home/user/website/node_modules/next/dist/server/base-server.js:1822:28)
              at async DevServer.renderToResponseWithComponentsImpl (/home/user/website/node_modules/next/dist/server/base-server.js:1832:28)
              at async DevServer.renderPageComponent (/home/user/website/node_modules/next/dist/server/base-server.js:2259:24)
              at async DevServer.renderToResponseImpl (/home/user/website/node_modules/next/dist/server/base-server.js:2297:32)
              at async DevServer.pipeImpl (/home/user/website/node_modules/next/dist/server/base-server.js:959:25)
              at async NextNodeServer.handleCatchallRenderRequest (/home/user/website/node_modules/next/dist/server/next-server.js:281:17)
              at async DevServer.handleRequestImpl (/home/user/website/node_modules/next/dist/server/base-server.js:853:17)
              at async /home/user/website/node_modules/next/dist/server/dev/next-dev-server.js:371:20
              at async Span.traceAsyncFn (/home/user/website/node_modules/next/dist/trace/trace.js:153:20)
              at async DevServer.handleRequest (/home/user/website/node_modules/next/dist/server/dev/next-dev-server.js:368:24)
              at async invokeRender (/home/user/website/node_modules/next/dist/server/lib/router-server.js:230:21)
              at async handleRequest (/home/user/website/node_modules/next/dist/server/lib/router-server.js:408:24)
              at async requestHandlerImpl (/home/user/website/node_modules/next/dist/server/lib/router-server.js:432:13)
              at async Server.requestListener (/home/user/website/node_modules/next/dist/server/lib/start-server.js:146:13)
      "data": {
        "errors": [
          {
            "message": "Value must be unique",
            "path": "id"
          }
        ]
      },
      "isOperational": true,
      "isPublic": false,
      "status": 400,
      "name": "ValidationError"
    }

I managed to fix it by changing the following line:

https://github.com/payloadcms/payload/blob/e4712a822b26d7a3fa643300f3610412404c0ea8/templates/website/src/search/beforeSync.ts#L30

With my fix, the return statement of the categories.map in beforeSyncWithSearch is now:

        return {
          relationTo: 'categories',
          // TODO: Document internal `id` handling
          category_id: id,
          title,
        }

I can't fully nail down why it triggered it, so any thought would be welcome:

  • We recently switched to Postgress as a data backend. I have tested reverting back to our previous adapter (sqlite). This would have saved me a bit of debuggin time, as the error message is a bit clearer as to what it refers:
    err: {
      "type": "Error",
      "message": "UNIQUE constraint failed: search_categories.id",
      "stack":
          Error: UNIQUE constraint failed: search_categories.id
              at Object.next (/home/user/website/node_modules/libsql/index.js:335:20)
              at Statement.all (/home/user/website/node_modules/libsql/index.js:360:16)
              at executeStmt (file:///home/user/website/node_modules/@libsql/client/lib-esm/sqlite3.js:261:34)
              at Sqlite3Client.execute (file:///home/user/website/node_modules/@libsql/client/lib-esm/sqlite3.js:79:16)
              at LibSQLPreparedQuery.values (webpack-internal:///(rsc)/./node_modules/drizzle-orm/libsql/session.js:193:59)
              at LibSQLPreparedQuery.all (webpack-internal:///(rsc)/./node_modules/drizzle-orm/libsql/session.js:137:29)
              at QueryPromise.all (webpack-internal:///(rsc)/./node_modules/drizzle-orm/sqlite-core/query-builders/insert.js:156:28)
              at QueryPromise.execute (webpack-internal:///(rsc)/./node_modules/drizzle-orm/sqlite-core/query-builders/insert.js:165:41)
              at QueryPromise.then (webpack-internal:///(rsc)/./node_modules/drizzle-orm/query-promise.js:26:17)
              at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
      "rawCode": 1555,
      "code": "SQLITE_CONSTRAINT_PRIMARYKEY",
      "libsqlError": true
    }
  • I also tried @daohall 's suggested fix, but just updating the field in fieldOverrides.ts (without any other alterations) gives me the same error. This is my fieldOverrides.ts, just the updated bit:
{
    label: 'Categories',
    name: 'categories',
    type: 'array',
    admin: {
      readOnly: true,
    },
    fields: [
      {
        name: 'relationTo',
        type: 'text',
      },
      {
        name: '_id', // Renamed from 'id' to '_id'
        type: 'text',

      },
      {
        name: 'title',
        type: 'text',
      },
    ],
  },

larsgeb avatar Feb 26 '25 08:02 larsgeb

I think this is a bug in payload and needs attention.

It seems nearly nobody uses payload-search with postgres in production with more than two posts of the same category.

The main problem is that "id" column of table "search_categories" is a primary key (unique), so it can´t be used to reference more than one category.

I´ve managed to fix it for the most part, but after changing category of a post it is necessary to manually reindex the search results via the UI. So keep that in mind.🧠

You will need to make these changes:

  1. search/beforSync.ts change the return statement to: https://github.com/payloadcms/payload/blob/bdf0113b2f52f9c7fc06118d4bf2025cec5ca527/templates/website/src/search/beforeSync.ts#L30
return {
  relationTo: "categories",
  // DO NOT include "id" here, it will be generated by the search plugin!
  categoryId: id, // add the id of the category as "categoryId"
  title,
};
  1. search/fielOverrides.ts change the categories field "id" to "categoryId": https://github.com/payloadcms/payload/blob/bdf0113b2f52f9c7fc06118d4bf2025cec5ca527/templates/website/src/search/fieldOverrides.ts#L52
  {
    label: "Categories",
    name: "categories",
    type: "array",
    admin: {
      readOnly: true,
    },
    fields: [
      {
        name: "relationTo",
        type: "text",
      },
      {
        name: "categoryId", // do not use name "id" as it will conflict with the search plugin
        type: "text",
      },
      {
        name: "title",
        type: "text",
      },
    ],
  },

Please let me know if the suggested changes have solved your problem. 🙏🏻

manuel-pchr avatar Feb 28 '25 08:02 manuel-pchr

It kind of does, though somehow my posts / pages are all getting 404-ed until I preview any of them from the admin dashboard. Can provide more details down the line, but maybe that already sounds like something obvious?

larsgeb avatar Mar 01 '25 13:03 larsgeb

manuel-pchr's answer has actually solved my problem. Thanks a lot 💖 @manuel-pchr

k1enttt avatar Mar 26 '25 10:03 k1enttt

None of these ideas fixed my problem.

Somehow the hook gets triggered twice and in the second call something seems to be off:

import { BeforeSync, DocToSync } from '@payloadcms/plugin-search/types'

export const beforeSyncWithSearch: BeforeSync = async ({ originalDoc, searchDoc }) => {
  const {
    doc: { relationTo: collection },
  } = searchDoc

  const { slug, id, categories, title, meta } = originalDoc

  const modifiedDoc: DocToSync = {
    ...searchDoc,
    slug,
    meta: {
      ...meta,
      title: meta?.title || title,
      image: meta?.image?.id || meta?.image,
      description: meta?.description,
    },
    categories: [],
  }

  if (categories && Array.isArray(categories) && categories.length > 0) {
    // get full categories and keep a flattened copy of their most important properties
    try {
      const mappedCategories = categories.map(category => {
        const { id, title } = category

        return {
          relationTo: 'categories',
          categoryId: id,
          title,
        }
      })

      modifiedDoc.categories = mappedCategories
    } catch (_err) {
      console.error(
        `Failed. Category not found when syncing collection '${collection}' with id: '${id}' to search.`,
      )
    }
  }

  console.log(modifiedDoc, 'modifiedDoc')

  return modifiedDoc
}

Leads to

 POST /admin/collections/posts/680352291600fcaff75099d0 200 in 92ms
{
  doc: { relationTo: 'posts', value: '680352291600fcaff75099d0' },
  title: 'tests',
  slug: 'tests',
  meta: { title: 'tests', image: undefined, description: undefined },
  categories: [
    {
      relationTo: 'categories',
      categoryId: '680360b53fa2b7559ceeefea',
      title: 'testCategory'
    }
  ]
} modifiedDoc
 PATCH /api/posts/680352291600fcaff75099d0?draft=true&autosave=true&locale=undefined 200 in 199ms
[10:44:18] INFO: Revalidating post at path: /tests
{
  doc: { relationTo: 'posts', value: '680352291600fcaff75099d0' },
  title: 'tests',
  slug: 'tests',
  meta: { title: 'tests', image: undefined, description: undefined },
  categories: [
    {
      relationTo: 'categories',
      categoryId: undefined,
      title: undefined
    }
  ]
} modifiedDoc

I tried to get behind the issue, and thought maybe a faulty ID is written due to some scoping issues. However, in the DB there is a category linked to the post in the search document - with an ID I can not find anywhere, its neither the categoryId, nor the postId, nor the searchId.

yannicschroeer avatar Apr 19 '25 08:04 yannicschroeer

@manuel-pchr in #9449 solved the same problem for me.

And it also helped me solve another problem: if I specify post's categories, the post may randomly fail to display under categories.It's OK now!

So great! Thank you!!

sd44 avatar May 06 '25 15:05 sd44

🚀 This is included in version v3.38.0

github-actions[bot] avatar May 15 '25 18:05 github-actions[bot]

This issue has been automatically locked. Please open a new issue if this issue persists with any additional detail.

github-actions[bot] avatar May 23 '25 05:05 github-actions[bot]