Search and sort by localized field does not work: Error: Alias "XXX_locales" is already used in this query

[lazidoca]

Link to reproduction

No response

Payload Version

3.0.0-beta.57

Node Version

20 lts

Next.js Version

15 rc 0

Describe the Bug

I have a posts collection with a localized title field. In the Admin panel, I want to filter by the title. It works if the sortby is unset (it will sort by posts.id I think). If I sort by the title, the error occurs:

Error: Alias "posts_locales" is already used in this query

Reproduction Steps

1. Create a posts collection with a localized title field
2. go to /admin/collections/posts
3. create a post record
4. enter to the search text input to filter by "title"
5. Sort by title

Adapters and Plugins

postgresAdapter using node-postgres driver

[lazidoca]

It seems like when there are multiple joins on the the same table, the chainedMethods is pushed with a new left join which causes the issue:

https://github.com/payloadcms/payload/blob/93bdc0e98d5541fddfe5a143e5678164692d3d37/packages/db-postgres/src/queries/selectDistinct.ts#L37C1-L43C1

[lazidoca]

Payload 3.0 beta 56 works fine, so I think the recent changes in beta 57 causes the issue

[yobottehg]

Just wanted to add here that this is still an issue. We have this on the image collection when we search there.

In our image collection the alt and caption fields are localized and the search field then yields an error:

admin/collections/image?limit=10&page=1&search=test

Yields

This is because we have the alt and caption in the listSearchableFields:

fields: [
    {
      name: 'alt',
      label: 'Alternative Text',
      localized: true,
      required: true,
      type: 'text',
      admin: {
        description: 'Alternative text for the image',
      },
    },
    {
      name: 'caption',
      label: 'Caption',
      localized: true,
      type: 'textarea',
      admin: {
        description: 'Description of the image',
      },
    },
  ...
 admin: {
    description: 'Collection used to store image files',
    useAsTitle: 'alt',
    defaultColumns: ['filename', 'alt', 'caption'],
    listSearchableFields: ['filename', 'alt', 'caption'],
  },

If i remove alt and caption from there it works but only searches in the filename.

Update: This also works if i only remove the caption from the listSearchableFields. So the "alt" somehow works.

[tiago-meireles]

I'm also having this issue. This is the error message that appears

      |  GET /admin/collections/pages?limit=10&sort=title 200 in 64ms
      |  ⨯ node_modules/@payloadcms/db-postgres/node_modules/drizzle-orm/pg-core/query-builders/select.js (94:1) @ PgSelectBase.eval [as leftJoin]
      |  ⨯ Error: Alias "_pages_v_locales" is already used in this query
      |     at Array.reduce (<anonymous>)
      | digest: "3529223665"
      |   92 |       const tableName = getTableLikeName(table);
      |   93 |       if (typeof tableName === "string" && this.config.joins?.some((join) => join.alias === tableName)) {
      | > 94 |         throw new Error(`Alias "${tableName}" is already used in this query`);
      |      | ^
      |   95 |       }
      |   96 |       if (!this.isPartialSelect) {
      |   97 |         if (Object.keys(this.joinsNotNullableMap).length === 1 && typeof baseTableName === "string") {

[r1tsuu]

Fixed in https://github.com/payloadcms/payload/pull/8396

[github-actions[bot]]

This issue has been automatically locked. Please open a new issue if this issue persists with any additional detail.

[github-actions[bot]]

🚀 This is included in version v3.0.0-beta.109