Problem

Queries in the Drizzle adapter would fail when filtering across multiple relationship fields that target the same collection or have nested fields with identical names.

This happened because the constraintPath was being used as the join deduplication key (queryPath). However, constraintPath is reset to an empty string whenever a query "jumps" to a new collection via a relationship field. This led to different logical paths (e.g., artist.categories and organizer.categories) sharing the same join identifier, causing Drizzle to incorrectly reuse the same table alias for distinct joins.

Solution

I have implemented a more robust absolute path tracking mechanism:

• Introduced a true queryPath: Added a queryPath parameter to getTableColumnFromPath that tracks the full absolute path from the root of the query. This ensures every unique logical path has a unique join identifier.
• Improved addJoinTable Deduplication: Updated the join utility to correctly distinguish between different paths targeting the same table.
• Consistent Join Handling: Updated all join-related operations (Arrays, Blocks, Relationships, Selects) to use the new queryPath.
• Deduplication for Simple Relationships: Added join deduplication for standard relationship fields to prevent redundant joins.
• Internal Suffixing: Added specific suffixes like ._locales and ._target to join identifiers to prevent collisions between main tables and their associated metadata tables.

Changes

• Modified packages/drizzle/src/queries/getTableColumnFromPath.ts to implement absolute path tracking.
• Modified packages/drizzle/src/queries/addJoinTable.ts to support queryPath based deduplication.

Fixes #15585