How to use subqueryExpression in a View?

[qmr777]

In the DAO layer, I can use the subqueryExpression method to query the children corresponding to a certain node. However, this is not possible in a View.

May I ask if there are any solutions to this issue?

  // it worked in DAO
  Future<List<TreeMapNode>> queryAllNodeByTreeId(String treeId) async {
    final childInfoAlias = alias(treeMapNodeInfoTable, 'ti');
    final childrenJsonArray =
        FunctionCallExpression<String>('json_group_array', [childInfoAlias.id]);
    final queryChildren = subqueryExpression<String>(selectOnly(childInfoAlias)
      ..addColumns([childrenJsonArray])
      ..where(childInfoAlias.parentId.equalsExp(treeMapNodeView.id))
      ..orderBy([OrderingTerm.asc(childInfoAlias.left)]));

    final stat = select(treeMapNodeView).join([])
      ..addColumns([queryChildren])
      ..where(treeMapNodeView.treeId.equals(treeId));
    final typedResult = await stat.get();
    final converter = StringListConverter();
    return typedResult.map((e) {
      final orderedChildren = converter.fromSql(e.read(queryChildren)!);

      return TreeMapNode(
          id: e.read(treeMapNodeView.id)!,
          treeId: e.read(treeMapNodeView.treeId)!,
          parentId: e.read(treeMapNodeView.parentId),
          title: e.read(treeMapNodeView.title)!,
          content: e.read(treeMapNodeView.content)!,
          orderedChildren: orderedChildren,
          hideChildren: e.read(treeMapNodeView.hideChildren) ?? false);
    }).toList();
  }

abstract class TreeMapNodeView extends View {
  TreeMapNodeInfoTable get nodeInfo;

  StandardContentTable get nodeContent;

  // TreeMapNodeInfoTable get childInfoAlias;
  //
  // Expression<String> get childrenJsonArray =>
  //     FunctionCallExpression<String>('json_group_array', [childInfoAlias.id]);
  //

  // --- I can't define a subQueryExpression here.

  // Expression<String> get queryChildren =>
  //     subqueryExpression<String>(
  //         select([childInfoAlias.id]).from(childInfoAlias)
  //           ..addColumns([childrenJsonArray])
  //           ..where((tbl) => tbl.)
  //           ..orderBy([OrderingTerm.asc(childInfoAlias.left)]));

  @override
  Query<HasResultSet, dynamic> as() =>
      select([
        nodeContent.id,
        nodeInfo.treeId,
        nodeInfo.parentId,
        nodeContent.title,
        nodeContent.content,
        nodeInfo.colorValue,
        nodeInfo.hideChildren,
        nodeInfo.destination,
        nodeInfo.extra,
      ]).from(nodeInfo).join([
        innerJoin(nodeContent, nodeContent.id.equalsExp(nodeInfo.id)),
      ])
        ..orderBy([OrderingTerm.asc(nodeInfo.left)]);
}

class TreeMapNodeInfoTable extends Table {
  TextColumn get id => text()();

  TextColumn get treeId => text().references(TreeMapInfoTable, #treeId)();

  TextColumn get parentId => text().nullable()();

  // ....

  @override
  Set<Column<Object>>? get primaryKey => {id};
}