Azure CosmosDB Error with Versioning & Relationship Fields

[Rhys97W]

Link to reproduction

https://github.com/MeridianTrustDev/meridian-cms/tree/test

Payload Version

3.0.0-beta.35

Node Version

20.11.1

Next.js Version

14.3.0-canary.68

Describe the Bug

When opening a relationship selection field where the target collection has versioning enabled, it throws "An error has occurred", writes the below to console and renders the field unusable. This was an issue previously and was resolved by this issue: https://github.com/payloadcms/payload/issues/4273 however has become an issue again and the issue still happens with

indexSortableFields: true

ERROR: MongoServerError: Error=2, Details='Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: 293065ad-8231-418f-9212-20e6d4a6a7e1; Reason: (Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: 293065ad-8231-418f-9212-20e6d4a6a7e1; Reason: (Response status code does not indicate success: BadRequest (400); Substatus: 0; ActivityId: 293065ad-8231-418f-9212-20e6d4a6a7e1; Reason: (Message: {"Errors":["The index path corresponding to the specified order-by item is excluded."]}
ActivityId: 293065ad-8231-418f-9212-20e6d4a6a7e1, Request URI: /apps/4e02fb2b-1420-474f-9ab7-65a6dc4d945a/services/0c0a0194-11ee-4f82-962f-a310a49d5737/partitions/61a8710b-8cce-4b94-8997-0acfb8201f47/replicas/133603308170502059s/, RequestStats: Microsoft.Azure.Cosmos.Tracing.TraceData.ClientSideRequestStatisticsTraceDatum, SDK: Windows/10.0.20348 cosmos-netstandard-sdk/3.18.0);););
    at Connection.onMessage (/Users/rhyswilliams/Github/meridian-cms/node_modules/mongodb/lib/cmap/connection.js:231:30)
    at MessageStream.<anonymous> (/Users/rhyswilliams/Github/meridian-cms/node_modules/mongodb/lib/cmap/connection.js:61:60)
    at MessageStream.emit (node:events:518:28)
    at processIncomingData (/Users/rhyswilliams/Github/meridian-cms/node_modules/mongodb/lib/cmap/message_stream.js:125:16)
    at MessageStream._write (/Users/rhyswilliams/Github/meridian-cms/node_modules/mongodb/lib/cmap/message_stream.js:33:9)
    at writeOrBuffer (node:internal/streams/writable:564:12)
    at _write (node:internal/streams/writable:493:10)
    at Writable.write (node:internal/streams/writable:502:10)
    at TLSSocket.ondata (node:internal/streams/readable:1007:22)
    at TLSSocket.emit (node:events:518:28)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
    at Readable.push (node:internal/streams/readable:390:5)
    at TLSWrap.onStreamRead (node:internal/stream_base_commons:190:23)
    at TLSWrap.callbackTrampoline (node:internal/async_hooks:130:17)

Reproduction Steps

To reproduce I navigate to any collection with a relationship field, attempt to open the relationship dropdown and it immedietly errors. I have narrowed down the issue to be caused between 3.0.0-beta.34 and 3.0.0-beta.35, whereby the fields work in 34 but break in 35.

Adapters and Plugins

db-mongodb, richtext-lexical, plugin-nested-docs, storage-azure, plugin-seo

[aliADSP]

We faced the same issue with CosmosDB and versioned collections. Once I disabled the versioning on collections, the problem was resolved. Any idea when this will be fixed? We can't enable the preview and versioning of collections otherwise.

[mlabrum]

EDIT2: So doing a rough patch (This probably breaks other things) and ignoring using the getQueryDraftsSort for the id field fixes the issue with id sorting, as something has broken the sort and it ends up being passed as "version.id" to sort on instead of _id

diff --git a/dist/collections/operations/find.js b/dist/collections/operations/find.js
index 2e28881c9b3e3adc35dd1f1ea22a5f386cece055..a40fc7e87dd671a6cecab6b35512dffdb8e44a25 100644
--- a/dist/collections/operations/find.js
+++ b/dist/collections/operations/find.js
@@ -84,6 +84,12 @@ async function find(incomingArgs) {
                 versionFields: (0, _buildCollectionFields.buildVersionCollectionFields)(collection.config),
                 where: fullWhere
             });
+
+            let sortTransform = sort;
+            if(sort !== "id"){
+                sortTransform = (0, _getQueryDraftsSort.getQueryDraftsSort)(sort)
+            }
+
             result = await payload.db.queryDrafts({
                 collection: collectionConfig.slug,
                 limit: sanitizedLimit,
@@ -91,7 +97,7 @@ async function find(incomingArgs) {
                 page: sanitizedPage,
                 pagination: usePagination,
                 req,
-                sort: (0, _getQueryDraftsSort.getQueryDraftsSort)(sort),
+                sort: sortTransform,
                 where: fullWhere
             });
         } else {

---

EDIT 1:

This broke the collection list... looks like defaultSort does not work with the collection list page and versions, it attempts to sort on a root field for the _versions mongo table which instead lives under the version field.... ugggh

---

Original comment:

I hit the same issue and it usually means that a field has not been indexed, the exact issue I hit was when selecting a relationship it would error due to a missing id index.

The url it was requesting was: api/pages?depth=0&draft=true&limit=10&page=1&sort=id

which wanted to sort on id, at least in my pages collection I had the useAsTitle and listSearchable fields set to url (Custom field) so adding defaultsort: "url" made payload now sort the collection by a known indexed field.

  admin: {
    useAsTitle: "url",
    listSearchableFields: ["url"],
    preview: (doc) => formatPreviewUrl("pages", doc),
  },
  defaultSort: "url",

Maybe a better way would be to figure out why id is not being indexed anymore, and how to add it, but this atleast got my site working again

[Rhys97W]

This is still an issue on the latest beta version, even when using the website template via create-payload-app@beta, straight out of the gate it errors with 400.

[github-actions[bot]]

🚀 This is included in version v3.0.0-beta.110

[github-actions[bot]]

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