Admin: Filtering on relationship field returns invalid input syntax error

[harolrodriguez]

Link to reproduction

https://github.com/payloadcms/website

Describe the Bug

When applying a filter to a relationship field in Payload CMS, the query is processed incorrectly, causing a syntax error in PostgreSQL.

Error message:

invalid input syntax for type integer: "{"2","2"}"

To Reproduce

Steps to reproduce the behavior:

1. Install Payload CMS demo using:
   npx create-payload-app@latest my-app
2. Select demo during setup and run the seed without modifications.
3. Configure the project to use PostgreSQL as the database.
4. Go to the Payload Web admin panel.
5. Navigate to the "Posts" collection.
6. Apply a filter on a relationship field.

Screen.Recording.2025-03-09.at.3.38.59.PM.mov

.

Payload Version

3.27.0

Adapters and Plugins

No response

[cdelacombaz]

I have the same issue. I'm using payload v3.32.0.

You can use this repo to reproduce it. There is a FAQ collection with relationships to Category (one) and Tags (many).

Filter to Category works fine.

Filter to Tags (many) results in the same error. This only happens with "equals" and "is not equal to" filters. "is in", "is not in" and "exist" work fine.

[harolrodriguez]

Do you have any alternative? @r1tsuu

[ruslantkach]

I'm experiencing the same issue with Payload CMS v3.34.0 using PostgreSQL.

I've implemented a tags system in my Contacts collection using a relationship field with hasMany: true pointing to a Tags collection:

{
  name: 'tags',
  type: 'relationship',
  relationTo: 'tags',
  hasMany: true,
  admin: {
    allowCreate: true,
    description: 'Select existing tags or create new ones',
  },
}

When trying to filter contacts by tags in the admin UI:

• Using the "equals" condition produces the error: invalid input syntax for type integer: "{"1","1"}"
• Using the "is in" condition works correctly

I've tried several approaches to resolve this issue:

1. Simplified Implementation: Removed complex field configurations and kept only a basic relationship field
2. Bi-directional Relationships: Implemented a Join field in the Tags collection pointing back to Contacts

None of these approaches resolved the issue with the "equals" filter. The error persists specifically with the "equals" and "is not equal to" conditions, while other conditions like "is in" work fine.

This appears to be a PostgreSQL-specific issue related to how array operations are handled in SQL queries. When using the "equals" condition, PostgreSQL tries to compare the entire array to a single value, generating invalid SQL syntax.

For now, I'm working around the issue by using the "is in" condition instead of "equals" when filtering by tags, but this is not ideal from a UX perspective since the UI suggests that "equals" should work.

Would it be possible to either:

1. Fix the SQL generation for "equals" conditions on relationship fields
2. Or disable/hide the "equals" and "is not equal to" conditions for these fields when using PostgreSQL to avoid confusion?

[iofreddy85]

I have the same issue. I'm using payload v3.32.0.

You can use this repo to reproduce it. There is a FAQ collection with relationships to Category (one) and Tags (many).

Filter to Category works fine.

Filter to Tags (many) results in the same error. This only happens with "equals" and "is not equal to" filters. "is in", "is not in" and "exist" work fine.

If the field is not required, in the filter is in, is not in if you select None, there's also an error.

filter: where[or][0][and][0][categoria][in][0]: null
error: error: invalid input syntax for type integer: "{"NaN","NaN"}".

It also happens if you go to filter exist and select true or false, and then you switch back to filter is in, the filter value true or false is not cleared and it errors
filter: where[or][0][and][0][categoria][in]: true

[cdelacombaz]

@harolrodriguez you added the label v2 to this issue . Isn't this marking this issue as a Payload v2 issue and therefore it might not get looked at?

It seems to me that here everyone is talking about payload v3

[harolrodriguez]

@cdelacombaz My bad! I mistakenly added the v2 label, but this is actually for v3.
Can I fix the label myself, or should someone from the team update it?

[cdelacombaz]

@harolrodriguez Can you edit the label yourself? If yes, maybe do so and tag the assignee to inform him. Else, tag the assignee and ask him to do it? Thx!

[harolrodriguez]

@r1tsuu Can you help me change the label from version 2 to 3? Is this a critical bug :(

[harolrodriguez]

@iofreddy85 @cdelacombaz @ruslantkach
I'm having the same issue and opened a new one here: #12252
Please comment there if you're affected too!