Skip to content

fix: hasMany text fields cannot be filtered with contains operator#15671

Merged
PatrikKozak merged 4 commits into
mainfrom
fix/db-mongodb-hasmany-contains
Feb 18, 2026
Merged

fix: hasMany text fields cannot be filtered with contains operator#15671
PatrikKozak merged 4 commits into
mainfrom
fix/db-mongodb-hasmany-contains

Conversation

@PatrikKozak

@PatrikKozak PatrikKozak commented Feb 18, 2026

Copy link
Copy Markdown
Contributor

What

Fixed contains operator for hasMany text fields in MongoDB and Drizzle adapters.

Why

Filtering hasMany text fields with contains failed with TypeError: formattedValue.replace is not a function because the code didn't handle array values.

How

  • MongoDB: Use $elemMatch with regex to search within array elements
  • Drizzle: Handle array values by creating OR conditions with LIKE matching

Fixes #15662

@github-actions

github-actions Bot commented Feb 18, 2026

Copy link
Copy Markdown
Contributor

📦 esbuild Bundle Analysis for payload

This analysis was generated by esbuild-bundle-analyzer. 🤖
This PR introduced no changes to the esbuild bundle! 🙌

@PatrikKozak PatrikKozak changed the title fix(db-mongodb): hasMany text fields cannot be filtered with contains operator fix: hasMany text fields cannot be filtered with contains operator Feb 18, 2026

Copilot AI left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Pull request overview

This pull request fixes a bug where filtering hasMany text fields with the contains operator caused a TypeError in both MongoDB and Drizzle adapters. The issue occurred because the code attempted to call .replace() on array values without proper type checking.

Changes:

  • Fixed MongoDB adapter to handle hasMany text/number/select fields with contains operator using appropriate query syntax
  • Fixed Drizzle adapter to handle array values in hasMany fields by creating OR conditions with LIKE matching
  • Added comprehensive integration and e2e tests for both single and multiple value contains queries

Reviewed changes

Copilot reviewed 6 out of 6 changed files in this pull request and generated 2 comments.

Show a summary per file
File Description
packages/db-mongodb/src/queries/sanitizeQueryValue.ts Added special handling for contains operator on hasMany text/number/select fields with regex matching
packages/drizzle/src/queries/sanitizeQueryValue.ts Added array value handling for hasMany fields with LIKE operator wrapping
packages/drizzle/src/queries/parseParams.ts Added OR condition logic for array values in hasMany contains queries
test/fields/int.spec.ts Added integration tests for contains operator with single and multiple values, minor import reordering
test/fields/collections/Text/e2e.spec.ts Added e2e tests for hasMany filtering in list view, minor import reordering
test/fields/payload-types.ts Updated generated types to include richTextField in ArrayField (unrelated schema update)

💡 Add Copilot custom instructions for smarter, more guided reviews. Learn how to get started.

Comment thread packages/db-mongodb/src/queries/sanitizeQueryValue.ts
Comment thread packages/db-mongodb/src/queries/sanitizeQueryValue.ts
@PatrikKozak PatrikKozak merged commit 4513a05 into main Feb 18, 2026
304 of 306 checks passed
@PatrikKozak PatrikKozak deleted the fix/db-mongodb-hasmany-contains branch February 18, 2026 18:55
@github-actions

Copy link
Copy Markdown
Contributor

🚀 This is included in version v3.77.0

AlessioGr added a commit that referenced this pull request Mar 6, 2026
…elds (#15865)

Fixes `contains` operator on hasMany `select` fields in PostgreSQL. This
was a regression introduced in #15671.

## The problem

Given a collection with a hasMany `select` field:

```typescript
{
  slug: 'users',
  fields: [
    {
      name: 'roles',
      type: 'select',
      hasMany: true,
      options: ['user', 'admin', 'editor'],
    },
  ],
}
```

Payload stores this across two PostgreSQL tables. For a user created
with `roles: ['admin', 'editor']`:

**`users`**
| id | created_at | updated_at |
|----|------------|------------|
| 1  | 2026-03-05 | 2026-03-05 |

**`users_roles`**
| id | parent_id | value    | order |
|----|-----------|----------|-------|
| 1  | 1         | `admin`  | 1     |
| 2  | 1         | `editor` | 2     |

The `value` column is a PostgreSQL **enum type**, not a `varchar`.

When querying `{ roles: { contains: 'admin' } }`, Payload generated:

```sql
SELECT DISTINCT "users"."id"
FROM "users"
LEFT JOIN "users_roles"
  ON "users"."id" = "users_roles"."parent_id"
WHERE "users_roles"."value" ILIKE '%admin%'
-- ❌ PostgreSQL error: operator does not exist: enum_users_roles ~~* unknown
```

`ILIKE` does not work on PostgreSQL enum types.

## The fix

The LEFT JOIN already flattens the array into individual rows:

| users.id | users_roles.value |
|----------|-------------------|
| 1        | `admin`           |
| 1        | `editor`          |

So "contains admin" just means "has a row where value equals admin". The
fix converts `contains` to `equals` for hasMany `select` fields, the
same way it already works for hasMany `relationship` and `upload`
fields:

```sql
SELECT DISTINCT "users"."id"
FROM "users"
LEFT JOIN "users_roles"
  ON "users"."id" = "users_roles"."parent_id"
WHERE "users_roles"."value" = 'admin'
-- ✅ Matches row 1, returns user id=1
```

## Why hasMany `text` fields are different

HasMany `text` fields store values as `varchar`, not as an enum. `ILIKE`
works fine on `varchar` columns, so substring matching (`contains:
'adm'` matching `'admin'`) is valid there. Whether it's _desirable_ is a
problem separate from this PR, being discussed internally
[here](https://payloadcms.slack.com/archives/C049BR3QBHC/p1772680331359859)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Projects

None yet

Development

Successfully merging this pull request may close these issues.

Text fields with hasMany: true cannot be filtered with contains operator

3 participants