payloadcms/payload

Count subquery for "join field" forgot to join after PR #11923

Closed this issue · 3 comments

NadhifRadityo commented

Describe the Bug

The original implementation in PR #11395 correctly counted related docs. But after PR #11923, the count subquery for join fields no longer includes the necessary JOINs.

Current Problem

PR #11923 updated document retrieval queries to include joins.forEach after building the query:

let query: SQLiteSelect = db
  .select(selectFields as any)
  .from(newAliasTable)
  .where(subQueryWhere)
  .orderBy(() => orderBy.map(({ column, order }) => order(column)))
  .$dynamic()

joins.forEach(({ type, condition, table }) => {
  query = query[type ?? 'leftJoin'](table, condition)
})

This works for retrieving documents. But the same logic was not applied to the count subquery, leaving it without the necessary joins.

Finding document like this

payload.find({
    collection: "product-categories",
    joins: { relatedProducts: { count: true } }
});

yields error

Error: Your "products_rels.path" field references a column "06c55f84_ef29_4b18_83fd_72f068fe0090"."path", but the table "06c55f84_ef29_4b18_83fd_72f068fe0090" is not part of the query! Did you forget to join it?
    at SQLiteAsyncDialect.buildSelectQuery (file:///E:/Projects/Web/payload-issue-count-subquery-does-not-include-proper-joins/node_modules/.pnpm/drizzle-orm@0.44.2_@libsql+client@0.14.0/node_modules/drizzle-orm/sqlite-core/dialect.js:220:15)
    at SQLiteSelectBase.getSQL (file:///E:/Projects/Web/payload-issue-count-subquery-does-not-include-proper-joins/node_modules/.pnpm/drizzle-orm@0.44.2_@libsql+client@0.14.0/node_modules/drizzle-orm/sqlite-core/query-builders/select.js:579:25)
    at SQLiteSelectBase.as (file:///E:/Projects/Web/payload-issue-count-subquery-does-not-include-proper-joins/node_modules/.pnpm/drizzle-orm@0.44.2_@libsql+client@0.14.0/node_modules/drizzle-orm/sqlite-core/query-builders/select.js:592:25)
    at file:///E:/Projects/Web/payload-issue-count-subquery-does-not-include-proper-joins/node_modules/.pnpm/@payloadcms+drizzle@3.57.0__830d129c4e601fbf6f4f9592c18517d9/node_modules/@payloadcms/drizzle/dist/find/traverseFields.js:494:108
    at Array.forEach (<anonymous>)
    at traverseFields (file:///E:/Projects/Web/payload-issue-count-subquery-does-not-include-proper-joins/node_modules/.pnpm/@payloadcms+drizzle@3.57.0__830d129c4e601fbf6f4f9592c18517d9/node_modules/@payloadcms/drizzle/dist/find/traverseFields.js:58:12)
    at buildFindManyArgs (file:///E:/Projects/Web/payload-issue-count-subquery-does-not-include-proper-joins/node_modules/.pnpm/@payloadcms+drizzle@3.57.0__830d129c4e601fbf6f4f9592c18517d9/node_modules/@payloadcms/drizzle/dist/find/buildFindManyArgs.js:30:5)
    at find (file:///E:/Projects/Web/payload-issue-count-subquery-does-not-include-proper-joins/node_modules/.pnpm/@payloadcms+drizzle@3.57.0__830d129c4e601fbf6f4f9592c18517d9/node_modules/@payloadcms/drizzle/dist/find/findMany.js:31:26)

Suggested Fix

Update the count subquery to also include the joins:

if (shouldCount) {
  currentArgs.extras[`${columnName}_count`] = sql`${db
    .select({
      count: count(),
    })
    .from(
      sql`${joins.reduce(
        (query, { type, condition, table }) =>
          query[type ?? 'leftJoin'](table, condition),
        db
          .select(selectFields as any)
          .from(newAliasTable)
          .where(subQueryWhere)
      )}.as(`${subQueryAlias}_count_subquery`)`,
    )}`.as(`${subQueryAlias}_count`)
}

There may be other cases of this issue introduced from PR 11923, but I haven't bothered to check.

Link to the code that reproduces this issue

https://github.com/NadhifRadityo/payload-issue-count-subquery-does-not-include-proper-joins

Reproduction Steps

  1. Clone this repo
  2. pnpm install
  3. pnpm run dev
  4. Visit http://localhost:3001/test

Which area(s) are affected? (Select all that apply)

area: core, db-postgres, db-sqlite, db-vercel-postgres

Environment Info

Binaries:
  Node: 24.0.2
  npm: N/A
  Yarn: N/A
  pnpm: N/A
Relevant Packages:
  payload: 3.57.0
  next: 15.4.4
  @payloadcms/email-nodemailer: 3.57.0
  @payloadcms/graphql: 3.57.0
  @payloadcms/next/utilities: 3.57.0
  @payloadcms/payload-cloud: 3.57.0
  @payloadcms/richtext-lexical: 3.57.0
  @payloadcms/translations: 3.57.0
  @payloadcms/ui/shared: 3.57.0
  react: 19.1.0
  react-dom: 19.1.0
Operating System:
  Platform: win32
  Arch: x64
  Version: Windows 10 Pro
  Available memory (MB): 16228
  Available CPU cores: 12
kwabe007 commented

Just ran into this issue today as well.
Making an api request without count on the join works:

GET {{baseURL}}/api/skills?depth=2&joins[offeredUsers][limit]=0
Response: 200 OK

With the count:

GET {{baseURL}}/api/skills?depth=2&joins[offeredUsers][limit]=0&joins[offeredUsers][count]=true
Response: 500 Internal Server Error

Payload console outputs:

 ERROR: Your "users_rels.path" field references a column "d31bedda_7848_4013_af9e_0e006593a2c0"."path", but the table "d31bedda_7848_4013_af9e_0e006593a2c0" is not part of the query! Did you forget to join it?
github-actions commented

🚀 This is included in version v3.59.0

github-actions commented

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