Skip to content

$expr inside relation filters can't reference the filtered model (missing FROM-clause entry) #2863

Closed

Description

@Natansal-weme

Description and expected behavior

$expr types its expression builder with the filtered model as the scope (ExpressionBuilder<DB, Model>), so qualified references like 'User.email' type-check. That holds for top-level filters, where the table is selected as "User". Relation filters, though, select the related model under a generated alias (e.g. "User" as "$$_Post$author"), so the same reference fails at runtime:

// works - top-level filter, the table is selected as "User"
await db.user.findMany({
    where: { $expr: (eb) => eb('User.email', 'like', '%@zenstack.dev') },
});

// type-checks, but fails with: missing FROM-clause entry for table "User"
await db.post.findMany({
    where: { author: { $expr: (eb) => eb('User.email', 'like', '%@zenstack.dev') } },
});

Unqualified references (eb('email', ...)) work because they resolve against the innermost table. They break down once the expression contains a subquery with a same-named column, e.g. a correlated subquery that needs the outer row's id, and $expr has no way to learn the real alias.

Expected: $expr gets the alias of the filtered model so expressions can qualify references, the same way computed field implementations already receive modelAlias in their context.

Proposed (non-breaking) fix: pass a second context argument with modelAlias to $expr:

await db.post.findMany({
    where: {
        author: {
            $expr: (eb, { modelAlias }) => eb(eb.ref(`${modelAlias}.email`), 'like', '%@zenstack.dev'),
        },
    },
});

I have a PR ready for this.

Environment (please complete the following information):

  • ZenStack version: 3.9.0 (the $expr call site is unchanged on dev)
  • Database type: PostgreSQL
  • Node.js/Bun version: 22.23.2
  • Package manager: pnpm

Additional context

The $expr callback is invoked in BaseCrudDialect.buildFilter as _where['$expr'](this.eb), while the modelAlias it needs is already in scope there.

Activity

  1. ymc9 commented on Oct 27, 2026

    @ymc9
    Member

    Released in v3.10.0 via #2864.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions