Filter Bugs

[tobiasiv]

Describe the Bug

Some filters either produce errors or behave in unintuitive ways.

Link to the code that reproduces this issue

https://github.com/tobiasiv/payload-reproduction/tree/filter

Reproduction Steps

1. Clone the reproduction repo (branch: filter)
2. Set the env variables
3. pnpm dev
4. Go to collection Products
5. Apply some filters, e.g.:

• CreatedAt "equals" current date: This returns an empty result, likely because selecting the date filter results in a different time. This behavior feels unintuitive.
• CreatedAt "is in" and "is not in": These filters always produce an error.
• CreatedAt: Changing any filter to or from "exists" produces an error.
• Categories "equals", "is not equal to": These filters always produce an error.
• Categories dropdown: The first time you open the selection dropdown, the correct suggestions are displayed. However, after switching filters, it only shows "None". This can be reset by selecting "exists" and then switching back to another filter.
• Categories: Changing any filter from "exists" causes an error, and switching to "exists" carries over the current filter value (instead of the expected default, such as "true"). For example, the number from the previous filter remains.
• Categories "is in" (multiple values): Selecting two values throws an error.
• Filter removal behavior: When adding and removing a filter, the filter area remains open. However, when values are filled out in a filter and it is removed, the filter area closes. It would be more consistent for the area to stay open in this case as well.

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

area: core

Environment Info

Binaries:
  Node: 22.13.0
  npm: N/A
  Yarn: N/A
  pnpm: 9.7.0
Relevant Packages:
  payload: 3.19.0
  next: 15.1.5
  @payloadcms/db-postgres: 3.19.0
  @payloadcms/email-nodemailer: 3.19.0
  @payloadcms/graphql: 3.19.0
  @payloadcms/next/utilities: 3.19.0
  @payloadcms/payload-cloud: 3.19.0
  @payloadcms/richtext-lexical: 3.19.0
  @payloadcms/ui/shared: 3.19.0
  react: 19.0.0
  react-dom: 19.0.0
Operating System:
  Platform: win32
  Arch: x64
  Version: Windows 11 Home
  Available memory (MB): 16263
  Available CPU cores: 8

[GermanJablo]

Thanks for reporting the issue. I've been able to reproduce the first 2 points (and I didn't continue with the next ones).

About the first one, currently the WhereBuilder does two things wrong:

1. It searches by UTC instead of adjusting for the user's time. If the user searches for documents created on day X, he should see the documents created that day in his time zone (even though they are stored in UTC in the DB).

2. If you search for an exact date, it seems to search by date and time, so no results ever appear unless they were created in the same millisecond.

@paulpopus is working on adding time zone support in Payload, so there will be progress on this soon.

About the second point, I was able to corroborate that it happens with Postgres but not with Mongodb.

In the repository linked the following link gives server error 500 when used with Postgres:
http://localhost:3000/api/products?where%5Bor%5D%5B0%5D%5Band%5D%5B0%5D%5BcreatedAt%5D%5Bin%5D=2025-01-27T12%3A00%3A00.000Z

Do you want to take a look, @r1tsuu?

[tobiasiv]

Hey @GermanJablo @paulpopus @r1tsuu, any updates on this one? Appreciate it!

[r1tsuu]

Hey @GermanJablo @paulpopus @r1tsuu, any updates on this one? Appreciate it!

As I understood, need to add support for 'like' for dates in Postgres. Quite not trivial, MongoDB does it for us on other side. PRs are welcome.

[tobiasiv]

Hey @GermanJablo @paulpopus @r1tsuu, any updates on this one? Appreciate it!

As I understood, need to add support for 'like' for dates in Postgres. Quite not trivial, MongoDB does it for us on other side. PRs are welcome.

I see, thanks for the clarification! A more intuitive date behavior is nice to have, but I understand why it’s not working at the moment. However, there are also the filter issues when dealing with relationships, like the 'Categories' examples I mentioned, which seem like a type error. There could even be more filter bugs when using postgres - I’ve only tried a few combinations so far. Any thoughts on that?