Payload CMS SQL Error: Localization With Drafts And Joins

Hey everyone! Today, let's dive into a tricky SQL query error that some of you might encounter while using Payload CMS, especially when dealing with localization, drafts, and join fields. This issue pops up when you're trying to fetch collections with the latest localization parameters enabled.

Understanding the Bug

So, here's the deal. When you've got the localizeStatus: true feature turned on and drafts are enabled, querying a collection that has a join field with draft: true and a locale parameter can lead to an invalid SQL query. This, in turn, causes a PostgreSQL error, specifically: "invalid reference to FROM-clause entry for table "_r_v"."

In simpler terms, the database gets confused about where to find a certain table reference in the query. This usually happens because of how the SQL query is constructed when dealing with joins, drafts, and localization all at once. Payload CMS tries to optimize the query, but in certain scenarios, it creates a query that PostgreSQL can't properly understand.

The core problem lies in how the database is trying to link different pieces of data together. Imagine you have a blog post (a document in Payload CMS) that is linked to an author (another document). When you're fetching the blog post, the system also needs to fetch the author's details. Now, add in the complexity of having different versions of the blog post for different languages (localization) and the ability to save drafts. When you query this setup, the SQL query becomes quite intricate, and sometimes the database loses track of which table it's supposed to be referencing.

To visualize this, think of it like ordering food at a restaurant. You want a burger (the main document), with a side of fries (the joined document). But then, you also want a special version of the burger based on your dietary needs (localization) and you're not quite sure yet, so it’s still a draft order. The waiter (the SQL query generator) needs to keep track of all these details, and sometimes they might get confused about which ingredients belong to which order. The error message is essentially the waiter saying, "I don't know which "_r_v" you're talking about!"

It’s worth noting that this issue is more likely to occur in complex data models where multiple collections are joined together and localization is heavily used. If you have a simple setup with just a few fields and no localization, you probably won't run into this problem. However, as your data model grows and becomes more sophisticated, you'll need to be aware of this potential pitfall.

Workarounds to the Rescue

Okay, so you've hit this bug. What can you do? Here are a few workarounds that have been found to help:

1. Comment out localizeStatus: true: This is a bit of a drastic measure, as it essentially disables the localization status feature. However, if you're in a pinch and need to get things working, it can be a temporary solution. Keep in mind that this will affect how your localized content is handled, so make sure you understand the implications before using this workaround.
2. Change name to label in the tabs field: There's a specific issue related to how tabs are named in your collection configuration. If you're using the name property instead of label, it can trigger this bug. Switching to label seems to resolve the issue in some cases. For example, in src/collections/Org.ts:17, make sure you're using label for your tabs.
3. Comment out defaultSort in the related collection: Another workaround involves commenting out the defaultSort property in the related collection. This might seem counterintuitive, but it appears that the default sorting can sometimes interfere with the SQL query generation. You can find an example of this in src/collections/Repo.ts:5.

These workarounds might not be ideal, but they can help you get your project running while the Payload CMS team works on a permanent fix.

Reproduction Steps

To reproduce this issue, you can follow these steps:

1. Clone the repository: https://github.com/nathanbowang/payload-localization-issues
2. Run the test in the repository.

The repository contains a minimal setup that demonstrates the bug. By running the test, you should be able to trigger the SQL query error and see the issue firsthand.

Here’s an image showing the error:

Affected Areas

This bug primarily affects the core area of Payload CMS, specifically the database querying and localization features.

Environment Information

Here's some environment info that might be helpful:

$ npx payload info

Binaries:
  Node: 24.11.1
  npm: 11.6.2
  Yarn: N/A
  pnpm: 10.25.0
Relevant Packages:
  payload: 3.72.0
  next: 15.4.10
  @payloadcms/db-postgres: 3.72.0
  @payloadcms/drizzle: 3.72.0
  @payloadcms/graphql: 3.72.0
  @payloadcms/next/utilities: 3.72.0
  @payloadcms/richtext-lexical: 3.72.0
  @payloadcms/translations: 3.72.0
  @payloadcms/ui/shared: 3.72.0
  react: 19.2.1
  react-dom: 19.2.1
Operating System:
  Platform: linux
  Arch: x64
  Version: #1 SMP PREEMPT_DYNAMIC Thu, 08 Jan 2026 10:03:06 +0000
  Available memory (MB): 31794
  Available CPU cores: 20
y

Key points:

• Payload CMS Version: 3.72.0
• Database Adapter: @payloadcms/db-postgres: 3.72.0
• Node Version: 24.11.1

Knowing the versions of these packages can help identify if the bug is specific to a certain version or if it's a more general issue.

Diving Deeper: Why Does This Happen?

Let's break down why this error occurs in more technical terms. The error message "invalid reference to FROM-clause entry for table "_r_v"" indicates that the PostgreSQL query planner is unable to resolve a table alias (_r_v) used in the query. This typically happens when the query involves multiple joins and subqueries, and the planner gets confused about the scope and visibility of the table alias.

When you enable localizeStatus: true, Payload CMS adds extra complexity to the SQL queries to handle the localization status of documents. This means that the queries need to fetch not only the main document but also its localized versions and their respective status (e.g., draft, published). Similarly, when you have a join field with draft: true, the queries need to handle drafts of the related documents as well.

All these factors combined can lead to a complex SQL query with multiple joins and subqueries. In some cases, the query planner might generate a plan that contains an invalid table alias reference, resulting in the error. The exact cause can vary depending on the specific data model and query parameters, but it's generally related to the interaction between localization, drafts, and joins.

Furthermore, the defaultSort option in related collections can also contribute to the issue. When you specify a default sort order, Payload CMS adds an ORDER BY clause to the SQL query. This clause can sometimes interfere with the query planner's ability to optimize the query, leading to the invalid table alias reference. By commenting out the defaultSort option, you might be able to simplify the query and avoid the error.

Potential Fixes and Future Improvements

While the workarounds mentioned earlier can help mitigate the issue, a permanent fix is needed to address the underlying cause. Here are some potential fixes and future improvements that the Payload CMS team could consider:

1. Optimize SQL Query Generation: The Payload CMS team could review the SQL query generation logic to ensure that it produces valid and efficient queries, even when dealing with localization, drafts, and joins. This might involve rewriting certain parts of the query generation code or using more advanced techniques to optimize the queries.
2. Improve Error Handling: The error message "invalid reference to FROM-clause entry for table "_r_v"" is not very informative and doesn't provide much guidance on how to fix the issue. The Payload CMS team could improve the error handling to provide more specific and helpful error messages that can help developers diagnose and resolve the problem more easily.
3. Add More Test Cases: The Payload CMS team could add more test cases that specifically cover scenarios involving localization, drafts, and joins. This would help ensure that the SQL query generation logic is thoroughly tested and that any potential issues are caught early on.
4. Consider Using a Different Query Builder: Payload CMS currently uses a custom SQL query builder. The team could consider using a more mature and well-tested query builder library, such as Knex.js or Sequelize. These libraries provide a higher level of abstraction and can help simplify the query generation process.

By addressing these potential fixes and future improvements, the Payload CMS team can make the platform more robust and reliable, especially when dealing with complex data models and localization scenarios.

Wrapping Up

So, there you have it! A deep dive into the SQL query error in Payload CMS when using localization with drafts and joins. Hopefully, this article has given you a better understanding of the issue, its causes, and potential workarounds. Keep an eye out for updates from the Payload CMS team, and happy coding!