Feature Request: Native "Left Anti-Join" / efficient excluding of relationships (Block functionality)

[sayem314]

I need to efficiently exclude documents based on the existence of a related document in another collection (a "Left Anti-Join").

Example Use Case: "Blocks" or "Muted Users" in a social app.

• users collection
• blocks collection: { blocker: relationship(users), blocked: relationship(users) }

Goal: Find all users EXCEPT those present in the blocks collection for the current user.

Current Limitations

1. Application-Side Filter (not_in):
   Fetch all blocked IDs -> where: { id: { not_in: [1000+ ids] } }.
   Issue: Hits URI/Memory limits with large block lists.
2. Denormalization:
   Add blockedBy array to User.
   Issue: High complexity to keep synced (syncing on create/delete/update).
3. Raw MongoDB Aggregation:
   Use $lookup in a custom endpoint.
   Issue: Bypasses Payload completely (loses afterRead hooks, virtuals, populates, and automatic typing).

Feature Request

A native Local API way to filter based on the absence of a relationship.

// Proposed Syntax Idea
await payload.find({
  collection: 'users',
  where: {
    // defined like a relationship filter
    join: {
      collection: 'blocks',
      on: { blocked: 'id' },
      where: { blocker: currentUserId },
      exists: false, // Key feature: Ensure NO match found
    },
  },
})

Is there any existing pattern to achieve this scalable "Anti-Join" without dropping down to raw DB adapters or denormalizing?