When using the json_group_array method, the orderBy clause is not worked.

[qmr777]

I have a dart table looks like this:

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

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

  IntColumn get left => integer()();

  IntColumn get right => integer()();

  IntColumn get depth => integer()();

  // rootNode if null
  TextColumn get parentId => text().nullable()();

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

And I also have a custom query that needs to return a list of certain nodes and their child nodes.
I hope the children ids can be sorted by the left column, but the OrderBy clause is not working.

The query looks like this:

  Future<List<TreeMapNode>> _queryTreeMapNode(
      Expression<bool> Function($TreeMapNodeInfoTableTable tbl) where) async {
    final childInfo = alias(treeMapNodeInfoTable, 'childInfo');

    // --- subquery for children id
    final childrenIds = subqueryExpression<String>(selectOnly(childInfo)
      ..addColumns([
        FunctionCallExpression<String>('json_group_array', [childInfo.id])
      ])
      ..where(childInfo.parentId.equalsExp(treeMapNodeInfoTable.id))
     // --- it doesn't work
      ..orderBy([OrderingTerm.asc(childInfo.left)]));

    final query = select(treeMapNodeInfoTable).join([
      innerJoin(standardContentTable,
          standardContentTable.id.equalsExp(treeMapNodeInfoTable.id))
    ])
      ..addColumns([childrenIds])
      ..where(where(treeMapNodeInfoTable))
      ..orderBy([OrderingTerm.asc(treeMapNodeInfoTable.left)]);

    final typedResult = await query.get();
    const converter = StringListConverter();

    return typedResult.map((e) {
      return TreeMapNode(
        id: e.read(treeMapNodeInfoTable.id)!,
        treeId: e.read(treeMapNodeInfoTable.treeId)!,
        parentId: e.read(treeMapNodeInfoTable.parentId),
        title: e.read(standardContentTable.title)!,
        content: e.read(standardContentTable.content)!,
        orderedChildren: converter.fromSql(e.read(childrenIds)!),
      );
    }).toList();
  }

For example, the data is like this:

I want to query the ids of the child nodes of the root node (where left == 0), and the correct query result should be ["TwPzNWxxS8OqxOxT0mR4Vg", "dUhWveP4Rpml1lvEGtcRRw", "Uzw16nTXSjy2EWYlM6_j2Q"].

However, the current query result is ["Uzw16nTXSjy2EWYlM6_j2Q", "TwPzNWxxS8OqxOxT0mR4Vg", "dUhWveP4Rpml1lvEGtcRRw"], which are output in the order they appear in the database.

[simolus3]

Thanks for starting this discussion! I think the problem is that the order by clause needs to be part of the aggregate function invocation, the order is technically still undefined even when using a statement-wide ordering.

Fixing this right now is not trivial since drift's query builder is not currently exporting a way to write aggregate functions (fixed in a68ec2a). So in the next drift release, you could simply do childInfo.id.jsonGroupArray(orderBy: OrderBy([OrderingTerm.asc(childInfo.left)])). Until then, I think the most viable option is to use a CustomExpression with the manual SQL invocation (e.g. json_group_array(childInfo.id ORDER BY childInfo.left ASC).

[qmr777]

It worked.Thanks!

current drift version: 2.17.0

before:

    final childrenIds = subqueryExpression<String>(selectOnly(childInfo)
      ..addColumns([
        FunctionCallExpression<String>('json_group_array', [childInfo.id])
      ])
      ..where(childInfo.parentId.equalsExp(treeMapNodeInfoTable.id))
      ..orderBy([OrderingTerm.asc(childInfo.left)]));

after:

    final childrenIds = subqueryExpression<String>(selectOnly(childInfo)
      ..addColumns([
        CustomExpression('json_group_array(childInfo.id ORDER BY childInfo.left ASC)')
      ])
      ..where(childInfo.parentId.equalsExp(treeMapNodeInfoTable.id)));