Postgres and drafts: bug in filtering, breaks relationship field

[Janisvb]

Link to reproduction

https://github.com/Janisvb/payload-reproduction/tree/filter-not-in

Describe the Bug

With Postgres and drafts enabled: when filtering with not_in, the following filter is ignored. This breaks the relationship field with hasMany:true, as you can't search for entries anymore.

To Reproduce

yarn dev:postgres _community

1. Only one filter (works correctly) http://localhost:3000/api/posts?draft=true&where[text][like]=2 returns example post 92, example post 82, example post 72, ...

2. Additional not_in filter (second filter is ignored) http://localhost:3000/api/posts?draft=true&where[id][not_in]=1&where[text][like]=2, returns example post 99, example post 98, example post 97, ...

Payload Version

2.11.1

Adapters and Plugins

db-postgres

[PatrikKozak]

Hey @Janisvb - thanks for including a reproduction! After trying to reproduce in main, the filters seem to be working as expected currently.

Can you give this another shot with latest and let me know if you're experiencing otherwise? Thank you!

[Janisvb]

Hi @PatrikKozak - yes, the problem still exists for me. I have updated my reproduction repo to the currrent main.

To reproduce, you can also do the following:

1. Create a collection with drafts enabled and one text field

export const PostsCollection: CollectionConfig = {
  versions: {drafts: true},
  admin: {useAsTitle: 'text'},
  fields: [{name: 'text', type: 'text'}],
  slug: 'posts',
}

2. Create some entries

for (let i = 0; i < 100; i++) {
  await payload.create({collection: 'posts', data: {text: 'example post ' + i}})
}

3. Search for entries via API and with id not_in filter http://localhost:3000/api/posts?draft=true&where[id][not_in]=1&where[text][like]=2 This returns example post 100, example post 99, ... but should only return example post 92, example post 82

[PatrikKozak]

@Janisvb Got it, you are filtering by ID not_in first. Yup, I was able to reproduce, I'll be looking into this.

[JPrisk]

Hope you don't mind me asking, is there any update here @PatrikKozak? holding off some additions which I could make an array of single relationships but would rather use hasMany on the one relationship field

Update: actually - using a single relationship doesn't work because when the user tries to "replace" the item rather than adding from an empty state we still encounter this issue

[github-actions[bot]]

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