[Bug]: error: missing FROM-clause entry when using orderBy with nested relation fields in a findMany query

[therealbrad]

Description and expected behavior

When using orderBy with nested relation fields in a findMany query, ZenStack v3 generates SQL with a table alias (e.g., TestRunStepResults$orderBy$0) that is not referenced in the FROM clause, causing PostgreSQL to reject the query with:

error: missing FROM-clause entry for table "TestRunStepResults$orderBy$0"

Expected behavior: The query should successfully execute and return results ordered by the nested relation fields.

Reproduction:

Schema (simplified):

model TestRunStepResults {
    id               Int             @id @default(autoincrement())
    testRunResultId  Int
    testRunResult    TestRunResults  @relation(fields: [testRunResultId], references: [id])
    stepId           Int
    step             Steps           @relation(fields: [stepId], references: [id], onDelete: Cascade)
    sharedStepItemId Int?
    sharedStepItem   SharedStepItem? @relation(fields: [sharedStepItemId], references: [id], onDelete: SetNull)
    statusId         Int
    // ...
}

model Steps {
    id    Int  @id @default(autoincrement())
    order Int  @default(0)
    // ...
}

model SharedStepItem {
    id    Int  @id @default(autoincrement())
    order Int
    // ...
}
Query that triggers the error:


const { data } = useFindManyTestRunStepResults({
  where: { testRunResultId: { in: resultIds } },
  orderBy: [
    { step: { order: "asc" } },
    { sharedStepItem: { order: "asc" } }
  ]
});
The same error also occurs with TestRunCases model:


error: missing FROM-clause entry for table "TestRunCases$orderBy$0"
Screenshots

N/A - server-side SQL error.

Environment (please complete the following information):

ZenStack version: 3.3.3
Database type: PostgreSQL 15
Node.js version: v24.13.0
Package manager: pnpm 10.6.3
Additional context

The error occurs because ZenStack's Kysely query builder generates an orderBy sub-select with a table alias like TestRunStepResults$orderBy$0, but this alias is not properly included in the query's FROM clause, causing PostgreSQL to reject it.

Current workaround: Remove the nested relation orderBy, fetch the ordering field via select/include, and sort client-side:


// Instead of server-side orderBy on relations:
const { data } = useFindManyTestRunStepResults({
  where: { testRunResultId: { in: resultIds } },
  // orderBy removed - causes "missing FROM-clause entry" error
  include: {
    step: { select: { order: true } },
    sharedStepItem: { select: { order: true } },
  }
});

// Sort client-side instead:
const sorted = useMemo(() => {
  if (!data) return [];
  return [...data].sort((a, b) => {
    const aOrder = a.step?.order ?? 0;
    const bOrder = b.step?.order ?? 0;
    if (aOrder !== bOrder) return aOrder - bOrder;
    return (a.sharedStepItem?.order ?? 0) - (b.sharedStepItem?.order ?? 0);
  });
}, [data]);

[genu]

Possibly related to #2367