Subquery<Row> class

A subquery allows reading from another complex query in a join.

An existing query can be constructed via DatabaseConnectionUser.select or DatabaseConnectionUser.selectOnly and then wrapped in Subquery to be used in another query.

For instance, assuming database storing todo items with optional categories (through a reference from todo items to categories), this query uses a subquery to count how many of the top-10 todo items (by length) are in each category:

 final longestTodos = Subquery(
  select(todosTable)
    ..orderBy([(row) => OrderingTerm.desc(row.title.length)])
    ..limit(10),
  's',
 );

 final itemCount = subquery.ref(todosTable.id).count();
 final query = select(categories).join([
   innerJoin(
     longestTodos,
     subquery.ref(todosTable.category).equalsExp(categories.id),
     useColumns: false,
   )])
     ..groupBy([categories.id])
     ..addColumns([itemCount]);

Note that the column from the subquery (here, the id of a todo entry) is not directly available in the outer query, it needs to be accessed through Subquery.ref. Columns added to the top-level query (via ref) can be accessed directly through TypedResult.read. When columns from a subquery are added to the top-level select as well, TypedResult.readTable can be used to read an entire row from the subquery. It returns a nested TypedResult for the subquery.

See also: subqueryExpression, for subqueries which only return one row and one column.