ORM: strongly typed relation predicates, ordering, and include customizers

[andrewzolotukhin]

Problem

While keeping database work in PostgreSQL in cleverbrush/xpenser#90, the ORM's current relation typing requires dropping to schema-backed queries for some ordinary joined reads.

Using 4.4.0, and inspecting the current upstream libs/orm/src/dbset.ts:

• include customizers receive SchemaQueryBuilder<any, any> instead of the selected relation's schema/result types.
• Root where and orderBy selectors target the root schema; there is no typed relation-aware API for ordering memberships by user.email or explicitly filtering roots by a related budget's archive status.
• Eager-loading builds an outer join around an ordered originalQuery CTE. An order inside that CTE does not guarantee the order of the final joined result.

The ORM already supports SQL count, whereIn, whereExists, whereRaw, and filtered belongsTo includes. This request is for type-safe relation query composition and final-result ordering, not basic SQL filtering or counting.

Suggested capability

Provide relation-aware predicates/existence checks and ordering using declared relation metadata, with customizer callbacks inferred from the target relation. For example, an API along the lines of whereHas(member => member.budget, budget => ...) and orderByRelated(member => member.user, user => user.email, 'asc') (names illustrative).

It should support these application cases without materializing rows in JavaScript:

1. Match a user's active budgets and check a case-insensitive exact display name, optionally excluding one budget.
2. List active/archived budgets, main budget first and then membership display name.
3. List budget memberships ordered by the related user's email.

Expected guarantees

• Related property selectors and include customizers retain schema-derived types; misspelled fields fail type checking.
• Related filters constrain the root rows in SQL, with clear semantics for optional and to-many relations.
• Ordering is applied to the final result; filtering precedes pagination.
• Mapped column names and projected relation join keys remain correct, including snake_case columns exposed as camelCase fields.
• Document generated SQL and cover these cases with SQL-compilation and PostgreSQL execution tests.

Current workaround

Xpenser keeps uniqueness checks and counts on ORM DbSets, using a database subquery for active budgets. Lists use @cleverbrush/knex-schema flat query projections with identifiers and row types derived from the registered schemas, preserving SQL filters and final ordering.

Related review: cleverbrush/xpenser#90 (comment)