Unable to use aggregate fields from SELECT in ORDER BY

[K-Mistele]

• I've validated the bug against the latest version of DB packages

Describe the bug
When I am using useLiveQuery with aggregate functions in the .select(({...}) => ({count: count(...), max: max(...)}) part of a query, I am unable to use these fields in a .orderBy or .having clause

To Reproduce
Steps to reproduce the behavior:

Perform a query involving an aggregate function with a groupBy or a having or an orderBy clause, and observe that it is only possible to use columns on the table(s) referenced in .from(), joins and sub-queries - not columns defined in .select() and/or aggregates.

const sessionStats = q
    .from({ sessions: sessionsCollection })
    .where(({ sessions }) => eq(sessions.archived, false))
    .groupBy(({ sessions }) => sessions.task_id)
    .select(({ sessions}) => ({
    	taskId: sessions.task_id,
    	latestActivity: max(sessions.last_activity_at),
    	sessionCount: count(sessions.id),
    }))
    .orderBy(({sessions}) => /* Problem here */)

Observe that while it is valid to order by sessions.<column> based on the types of the orderBy, it is NOT possible to order by the fields of latestActivity or sessionCount defined by the aggregate functions of max() and count(), or by other fields defined in the select query - the types indicate it is only possible to filter based on the properties of the rows of the table(s)/joins/subqueries used in the query.

Similarly, it is impossible to filter with .having() based on .select()-defined fields and aggregates - it is only possible to use columns of the table(s) used in the .from() clause, subqueries and joins.

Expected behavior
I should be able to use .orderBy() and .having() based on the fields and aggregate function-defined fields from the .select() clause

Screenshots
n/a

Desktop (please complete the following information):

• OS: MacOS
• Browser: n/a
• Version: n/a

Smartphone (please complete the following information):
n/a

Additional context
n/a

[thruflo]

I've come across the same limitation when trying to orderBy aggregated values from the select. One workaround is to build a first query with a select and then pass that as the from: ... value to a subsequent query.

This works and avoids materialization of the interim results.

E.g.:

const messageCollection = createLiveQueryCollection({
  query: (q) => {
    const baseQuery = q
      .from({ chunk: chunksCollection })
      .groupBy(({ chunk }) => chunk.messageId)
      .select(({ chunk }) => ({
        messageId: chunk.messageId,
        rows: collect(chunk),
        startedAt: minStr(chunk.createdAt)
      }))

    return q
      .from({ message: baseQuery })
      .orderBy(({ message }) => message.startedAt, 'asc')
  }
})