Skip to content

Commit 888ad6a

Browse files
kevin-dpautofix-ci[bot]
kevin-dp
and
autofix-ci[bot]
authored
Support references to aggregated fields from SELECT in ORDERBY and HAVING (#1094)
* Add unit tests that reproduce bug with references to fields from select clause. * ci: apply automated fixes * Handle refs to fields from select clause in replaceAggregatesByRefs * ci: apply automated fixes * Expose selected fields on a special namespace * ci: apply automated fixes * Fix rebase issue * ci: apply automated fixes * Rename __select_results to to avoid the additional transformation * ci: apply automated fixes * Fix types in tests * Add selected fields as a selected namespace on the type of orderBy/having clauses * ci: apply automated fixes * Add unit tests for functional having clause referencing fields from select * Fix type of having clause * ci: apply automated fixes * Update docs * Changeset --------- Co-authored-by: autofix-ci[bot] <114827586+autofix-ci[bot]@users.noreply.github.com>
1 parent cc10ad3 commit 888ad6a

14 files changed

Lines changed: 741 additions & 62 deletions

File tree

‎.changeset/weak-colts-walk.md‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
---
2+
'@tanstack/db': patch
3+
---
4+
5+
Introduce $selected namespace for accessing fields from SELECT clause inside ORDER BY and HAVING clauses.

‎docs/guides/live-queries.md‎

Lines changed: 33 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1108,10 +1108,19 @@ having(
11081108
```
11091109

11101110
**Parameters:**
1111-
- `condition` - A callback function that receives the aggregated row object and returns a boolean expression
1111+
- `condition` - A callback function that receives table references (and `$selected` if the query contains a `select()` clause) and returns a boolean expression
11121112

11131113
```ts
1114+
// Using aggregate functions directly
11141115
const highValueCustomers = createLiveQueryCollection((q) =>
1116+
q
1117+
.from({ order: ordersCollection })
1118+
.groupBy(({ order }) => order.customerId)
1119+
.having(({ order }) => gt(sum(order.amount), 1000))
1120+
)
1121+
1122+
// Using SELECT fields via $selected (recommended when select() is used)
1123+
const highValueCustomersWithSelect = createLiveQueryCollection((q) =>
11151124
q
11161125
.from({ order: ordersCollection })
11171126
.groupBy(({ order }) => order.customerId)
@@ -1120,7 +1129,7 @@ const highValueCustomers = createLiveQueryCollection((q) =>
11201129
totalSpent: sum(order.amount),
11211130
orderCount: count(order.id),
11221131
}))
1123-
.having(({ order }) => gt(sum(order.amount), 1000))
1132+
.having(({ $selected }) => gt($selected.totalSpent, 1000))
11241133
)
11251134
```
11261135

@@ -1388,6 +1397,26 @@ const sortedUsers = createLiveQueryCollection((q) =>
13881397
)
13891398
```
13901399

1400+
### Ordering by SELECT Fields
1401+
1402+
When you use `select()` with aggregates or computed values, you can order by those fields using the `$selected` namespace:
1403+
1404+
```ts
1405+
const topCustomers = createLiveQueryCollection((q) =>
1406+
q
1407+
.from({ order: ordersCollection })
1408+
.groupBy(({ order }) => order.customerId)
1409+
.select(({ order }) => ({
1410+
customerId: order.customerId,
1411+
totalSpent: sum(order.amount),
1412+
orderCount: count(order.id),
1413+
latestOrder: max(order.createdAt),
1414+
}))
1415+
.orderBy(({ $selected }) => $selected.totalSpent, 'desc')
1416+
.limit(10)
1417+
)
1418+
```
1419+
13911420
### Descending Order
13921421

13931422
Use `desc` for descending order:
@@ -1908,8 +1937,8 @@ const highValueCustomers = createLiveQueryCollection((q) =>
19081937
totalSpent: sum(order.amount),
19091938
orderCount: count(order.id),
19101939
}))
1911-
.fn.having((row) => {
1912-
return row.totalSpent > 1000 && row.orderCount >= 3
1940+
.fn.having(({ $selected }) => {
1941+
return $selected.totalSpent > 1000 && $selected.orderCount >= 3
19131942
})
19141943
)
19151944
```

‎docs/reference/classes/BaseQueryBuilder.md‎

Lines changed: 27 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -91,7 +91,11 @@ A QueryBuilder with functional having filter applied
9191
query
9292
.from({ posts: postsCollection })
9393
.groupBy(({posts}) => posts.userId)
94-
.fn.having(row => row.count > 5)
94+
.select(({posts}) => ({
95+
userId: posts.userId,
96+
postCount: count(posts.id),
97+
}))
98+
.fn.having(({ $selected }) => $selected.postCount > 5)
9599
```
96100

97101
###### select()
@@ -428,6 +432,17 @@ query
428432
.groupBy(({orders}) => orders.customerId)
429433
.having(({orders}) => gt(avg(orders.total), 100))
430434

435+
// Filter using SELECT fields via $selected
436+
query
437+
.from({ orders: ordersCollection })
438+
.groupBy(({orders}) => orders.customerId)
439+
.select(({orders}) => ({
440+
customerId: orders.customerId,
441+
totalSpent: sum(orders.amount),
442+
orderCount: count(orders.id),
443+
}))
444+
.having(({ $selected }) => gt($selected.totalSpent, 1000))
445+
431446
// Multiple having calls are ANDed together
432447
query
433448
.from({ orders: ordersCollection })
@@ -719,6 +734,17 @@ query
719734
.from({ users: usersCollection })
720735
.orderBy(({users}) => users.createdAt, 'desc')
721736

737+
// Sort by SELECT fields via $selected
738+
query
739+
.from({ posts: postsCollection })
740+
.groupBy(({posts}) => posts.userId)
741+
.select(({posts}) => ({
742+
userId: posts.userId,
743+
postCount: count(posts.id),
744+
latestPost: max(posts.createdAt),
745+
}))
746+
.orderBy(({ $selected }) => $selected.postCount, 'desc')
747+
722748
// Multiple sorts (chain orderBy calls)
723749
query
724750
.from({ users: usersCollection })

‎packages/db/src/query/builder/index.ts‎

Lines changed: 22 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -17,7 +17,11 @@ import {
1717
QueryMustHaveFromClauseError,
1818
SubQueryMustHaveFromClauseError,
1919
} from '../../errors.js'
20-
import { createRefProxy, toExpression } from './ref-proxy.js'
20+
import {
21+
createRefProxy,
22+
createRefProxyWithSelected,
23+
toExpression,
24+
} from './ref-proxy.js'
2125
import type { NamespacedRow, SingleResult } from '../../types.js'
2226
import type {
2327
Aggregate,
@@ -31,6 +35,7 @@ import type {
3135
CompareOptions,
3236
Context,
3337
GetResult,
38+
FunctionalHavingRow,
3439
GroupByCallback,
3540
JoinOnCallback,
3641
MergeContextForJoinCallback,
@@ -408,7 +413,12 @@ export class BaseQueryBuilder<TContext extends Context = Context> {
408413
*/
409414
having(callback: WhereCallback<TContext>): QueryBuilder<TContext> {
410415
const aliases = this._getCurrentAliases()
411-
const refProxy = createRefProxy(aliases) as RefsForContext<TContext>
416+
// Add $selected namespace if SELECT clause exists
417+
const refProxy = (
418+
this.query.select
419+
? createRefProxyWithSelected(aliases)
420+
: createRefProxy(aliases)
421+
) as RefsForContext<TContext>
412422
const expression = callback(refProxy)
413423

414424
// Validate that the callback returned a valid expression
@@ -506,7 +516,12 @@ export class BaseQueryBuilder<TContext extends Context = Context> {
506516
options: OrderByDirection | OrderByOptions = `asc`,
507517
): QueryBuilder<TContext> {
508518
const aliases = this._getCurrentAliases()
509-
const refProxy = createRefProxy(aliases) as RefsForContext<TContext>
519+
// Add $selected namespace if SELECT clause exists
520+
const refProxy = (
521+
this.query.select
522+
? createRefProxyWithSelected(aliases)
523+
: createRefProxy(aliases)
524+
) as RefsForContext<TContext>
510525
const result = callback(refProxy)
511526

512527
const opts: CompareOptions =
@@ -771,7 +786,7 @@ export class BaseQueryBuilder<TContext extends Context = Context> {
771786
* Filter grouped rows using a function that operates on each aggregated row
772787
* Warning: This cannot be optimized by the query compiler
773788
*
774-
* @param callback - A function that receives an aggregated row and returns a boolean
789+
* @param callback - A function that receives an aggregated row (with $selected when select() was called) and returns a boolean
775790
* @returns A QueryBuilder with functional having filter applied
776791
*
777792
* @example
@@ -780,11 +795,12 @@ export class BaseQueryBuilder<TContext extends Context = Context> {
780795
* query
781796
* .from({ posts: postsCollection })
782797
* .groupBy(({posts}) => posts.userId)
783-
* .fn.having(row => row.count > 5)
798+
* .select(({posts}) => ({ userId: posts.userId, count: count(posts.id) }))
799+
* .fn.having(({ $selected }) => $selected.count > 5)
784800
* ```
785801
*/
786802
having(
787-
callback: (row: TContext[`schema`]) => any,
803+
callback: (row: FunctionalHavingRow<TContext>) => any,
788804
): QueryBuilder<TContext> {
789805
return new BaseQueryBuilder({
790806
...builder.query,

‎packages/db/src/query/builder/ref-proxy.ts‎

Lines changed: 90 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -175,6 +175,96 @@ export function createRefProxy<T extends Record<string, any>>(
175175
return rootProxy
176176
}
177177

178+
/**
179+
* Creates a ref proxy with $selected namespace for SELECT fields
180+
*
181+
* Adds a $selected property that allows accessing SELECT fields via $selected.fieldName syntax.
182+
* The $selected proxy creates paths like ['$selected', 'fieldName'] which directly reference
183+
* the $selected property on the namespaced row.
184+
*
185+
* @param aliases - Array of table aliases to create proxies for
186+
* @returns A ref proxy with table aliases and $selected namespace
187+
*/
188+
export function createRefProxyWithSelected<T extends Record<string, any>>(
189+
aliases: Array<string>,
190+
): RefProxy<T> & T & { $selected: SingleRowRefProxy<any> } {
191+
const baseProxy = createRefProxy(aliases)
192+
193+
// Create a proxy for $selected that prefixes all paths with '$selected'
194+
const cache = new Map<string, any>()
195+
196+
function createSelectedProxy(path: Array<string>): any {
197+
const pathKey = path.join(`.`)
198+
if (cache.has(pathKey)) {
199+
return cache.get(pathKey)
200+
}
201+
202+
const proxy = new Proxy({} as any, {
203+
get(target, prop, receiver) {
204+
if (prop === `__refProxy`) return true
205+
if (prop === `__path`) return [`$selected`, ...path]
206+
if (prop === `__type`) return undefined
207+
if (typeof prop === `symbol`) return Reflect.get(target, prop, receiver)
208+
209+
const newPath = [...path, String(prop)]
210+
return createSelectedProxy(newPath)
211+
},
212+
213+
has(target, prop) {
214+
if (prop === `__refProxy` || prop === `__path` || prop === `__type`)
215+
return true
216+
return Reflect.has(target, prop)
217+
},
218+
219+
ownKeys(target) {
220+
return Reflect.ownKeys(target)
221+
},
222+
223+
getOwnPropertyDescriptor(target, prop) {
224+
if (prop === `__refProxy` || prop === `__path` || prop === `__type`) {
225+
return { enumerable: false, configurable: true }
226+
}
227+
return Reflect.getOwnPropertyDescriptor(target, prop)
228+
},
229+
})
230+
231+
cache.set(pathKey, proxy)
232+
return proxy
233+
}
234+
235+
const wrappedSelectedProxy = createSelectedProxy([])
236+
237+
// Wrap the base proxy to also handle $selected access
238+
return new Proxy(baseProxy, {
239+
get(target, prop, receiver) {
240+
if (prop === `$selected`) {
241+
return wrappedSelectedProxy
242+
}
243+
return Reflect.get(target, prop, receiver)
244+
},
245+
246+
has(target, prop) {
247+
if (prop === `$selected`) return true
248+
return Reflect.has(target, prop)
249+
},
250+
251+
ownKeys(target) {
252+
return [...Reflect.ownKeys(target), `$selected`]
253+
},
254+
255+
getOwnPropertyDescriptor(target, prop) {
256+
if (prop === `$selected`) {
257+
return {
258+
enumerable: true,
259+
configurable: true,
260+
value: wrappedSelectedProxy,
261+
}
262+
}
263+
return Reflect.getOwnPropertyDescriptor(target, prop)
264+
},
265+
}) as RefProxy<T> & T & { $selected: SingleRowRefProxy<any> }
266+
}
267+
178268
/**
179269
* Converts a value to an Expression
180270
* If it's a RefProxy, creates a Ref, otherwise creates a Value

‎packages/db/src/query/builder/types.ts‎

Lines changed: 26 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -345,6 +345,26 @@ export type JoinOnCallback<TContext extends Context> = (
345345
refs: RefsForContext<TContext>,
346346
) => any
347347

348+
/**
349+
* FunctionalHavingRow - Type for the row parameter in functional having callbacks
350+
*
351+
* Functional having callbacks receive a namespaced row that includes:
352+
* - Table data from the schema (when available)
353+
* - $selected: The SELECT result fields (when select() has been called)
354+
*
355+
* After `select()` is called, this type includes `$selected` which provides access
356+
* to the SELECT result fields via `$selected.fieldName` syntax.
357+
*
358+
* Note: When used with GROUP BY, functional having receives `{ $selected: ... }` with the
359+
* aggregated SELECT results. When used without GROUP BY, it receives the full namespaced row
360+
* which includes both table data and `$selected`.
361+
*
362+
* Example: `({ $selected }) => $selected.sessionCount > 2`
363+
* Example (no GROUP BY): `(row) => row.user.salary > 70000 && row.$selected.user_count > 2`
364+
*/
365+
export type FunctionalHavingRow<TContext extends Context> = TContext[`schema`] &
366+
(TContext[`result`] extends object ? { $selected: TContext[`result`] } : {})
367+
348368
/**
349369
* RefProxyForContext - Creates ref proxies for all tables/collections in a query context
350370
*
@@ -364,6 +384,9 @@ export type JoinOnCallback<TContext extends Context> = (
364384
*
365385
* The logic prioritizes optional chaining by always placing `undefined` outside when
366386
* a type is both optional and nullable (e.g., `string | null | undefined`).
387+
*
388+
* After `select()` is called, this type also includes `$selected` which provides access
389+
* to the SELECT result fields via `$selected.fieldName` syntax.
367390
*/
368391
export type RefsForContext<TContext extends Context> = {
369392
[K in keyof TContext[`schema`]]: IsNonExactOptional<
@@ -383,7 +406,9 @@ export type RefsForContext<TContext extends Context> = {
383406
: // T is exactly undefined, exactly null, or neither optional nor nullable
384407
// Wrap in RefProxy as-is (includes exact undefined, exact null, and normal types)
385408
Ref<TContext[`schema`][K]>
386-
}
409+
} & (TContext[`result`] extends object
410+
? { $selected: Ref<TContext[`result`]> }
411+
: {})
387412

388413
/**
389414
* Type Detection Helpers

‎packages/db/src/query/compiler/evaluators.ts‎

Lines changed: 38 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -95,12 +95,48 @@ function compileExpressionInternal(
9595
* Compiles a reference expression into an optimized evaluator
9696
*/
9797
function compileRef(ref: PropRef): CompiledExpression {
98-
const [tableAlias, ...propertyPath] = ref.path
98+
const [namespace, ...propertyPath] = ref.path
9999

100-
if (!tableAlias) {
100+
if (!namespace) {
101101
throw new EmptyReferencePathError()
102102
}
103103

104+
// Handle $selected namespace - references SELECT result fields
105+
if (namespace === `$selected`) {
106+
// Access $selected directly
107+
if (propertyPath.length === 0) {
108+
// Just $selected - return entire $selected object
109+
return (namespacedRow) => (namespacedRow as any).$selected
110+
} else if (propertyPath.length === 1) {
111+
// Single property access - most common case
112+
const prop = propertyPath[0]!
113+
return (namespacedRow) => {
114+
const selectResults = (namespacedRow as any).$selected
115+
return selectResults?.[prop]
116+
}
117+
} else {
118+
// Multiple property navigation (nested SELECT fields)
119+
return (namespacedRow) => {
120+
const selectResults = (namespacedRow as any).$selected
121+
if (selectResults === undefined) {
122+
return undefined
123+
}
124+
125+
let value: any = selectResults
126+
for (const prop of propertyPath) {
127+
if (value == null) {
128+
return value
129+
}
130+
value = value[prop]
131+
}
132+
return value
133+
}
134+
}
135+
}
136+
137+
// Handle table alias namespace (existing logic)
138+
const tableAlias = namespace
139+
104140
// Pre-compile the property path navigation
105141
if (propertyPath.length === 0) {
106142
// Simple table reference

0 commit comments

Comments
 (0)