Skip to content

twenty/v2.31.5

twenty/v2.31.5: ORM v2: relation-keyed where clauses (#24230)
@charlesBochet charlesBochet tagged this 16 Aug 08:16 
## What

ORM v2 resolved every top-level key of a `where` object as a column on
the main table, so a relation-keyed filter threw before any SQL ran:

```
where: { calendarEventParticipants: { personId: Any(personIds) } }
→ Column "calendarEventParticipants" does not exist on "calendarEvent"
```

`applyWhere` handed the object straight to `queryBuilder.where()`.
#24225 taught the **order** path about relation keys
(`isRelationOrderEntry` → `applyRelationOrderEntry`) but the **where**
path never learned.

The reference consumer is the record Timeline panel:
`timeline-calendar-event.service.ts` filters that way in both its
`count` and its paginated `find`, so under `IS_ORM_V2_READ_PATH_ENABLED`
both 500'd and the tab rendered "No Events". A sweep over `modules/**`
and `engine/core-modules/**` found these two call sites are the only
relation-keyed wheres on an ORM v2 repository in the server; everything
else that looks similar is either a core TypeORM entity or a composite
field (`domainName: { primaryLinkUrl }`).

## Approach

Relation keys render as a correlated `EXISTS`, not a join:

```sql
EXISTS (SELECT 1 FROM "workspace_x"."company" AS "person_people_filter"
        WHERE "person_people_filter"."personId" = "person"."id"
          AND ("person_people_filter"."name" = $1)
          AND "person_people_filter"."deletedAt" IS NULL)
```

A join would multiply parent rows for a to-many relation and break the
`count` and the `skip`/`take` in that same service. `EXISTS` keeps one
row per parent, so no `DISTINCT` is needed anywhere.

**Why a placeholder token.** Row-level permission predicates are
injected per alias in `onBeforeExecute`, which runs *after* the where
clause has been built into SQL strings. A subquery rendered eagerly at
`.where()` time would silently carry no RLS predicate, which is a
permission regression rather than a parity gap, since v1 joins the
relation and the joined alias picks the predicate up. So the where
clause holds a token and the statement renders it at build time, once
the predicates have landed.

**Permission model.** The filtered alias is exposed through
`expressionMap.joinAttributes` and `getJoinedTableShape`, so
`applyRowLevelPermissionPredicates` visits it and `addJoinCondition`
routes the predicate into the subquery. Referenced-column collection is
deliberately left alone: v1 collected from selects and order bys only,
never from where clauses, and collecting more than v1 breaks legitimate
system reads.

Nested fragments (`substituteExistsFilters: false`) keep their tokens so
the outermost render emits each subquery exactly once, which is what
makes OR groups and relation-in-relation filters compose. Soft-deleted
rows are excluded inside the subquery unless `withDeleted()`. Mutation
statements substitute tokens too, so a relation-keyed criteria can never
leak an unrendered placeholder into an `UPDATE` or `DELETE`.

## Testing

- `npx jest twenty-orm-v2`: 178 tests green across 17 suites, including
11 new ones covering to-many and to-one correlation, sibling conditions,
OR groups, relation-in-relation nesting, `withDeleted`, RLS injection
landing inside the subquery, join-attribute exposure, `getCount`
emitting no join and no `DISTINCT`, operator passthrough, and the
unresolvable-inverse-foreign-key error.
- Full `twenty-server` unit suite: 6022 passed. The 4 failing suites
(`get-standard-object-metadata-related-entity-ids`,
`assert-workspace-member-update-values-are-valid`,
`compute-calendar-event-standard-metadata`,
`compute-call-recording-standard-metadata`) fail identically on pristine
`origin/main` and none of them import `twenty-orm-v2`.
- `npx nx lint:diff-with-main twenty-server` and typecheck clean.

Not yet exercised against a live database with the flag on; the calendar
timeline path is covered here at the SQL level only.

<!-- This is an auto-generated description by cubic. -->
<a
href="https://cubic.dev/pr/twentyhq/twenty/pull/24230?utm_source=github"
target="_blank" rel="noopener noreferrer"
data-no-image-dialog="true"><picture><source
media="(prefers-color-scheme: dark)"
srcset="https://www.cubic.dev/buttons/review-in-cubic-dark.svg"><source
media="(prefers-color-scheme: light)"
srcset="https://www.cubic.dev/buttons/review-in-cubic-light.svg"><img
alt="Review in cubic"
src="https://www.cubic.dev/buttons/review-in-cubic-dark.svg"></picture></a>
<!-- End of auto-generated description by cubic. -->

(cherry picked from commit 044fc2d38908f711f5f073c345b656e13b541b8b)
Assets 2
Loading