withAggregates method

MssqlProjectedQuery<(TRow, Map<String, Object?>)> withAggregates(List<MssqlRelationAggregate<TRow>> aggregates(TFields fields))

Several independent correlated aggregates on one parent SELECT.

Each spec is its own subquery. Putting orders and lines in one join would make the order count equal the line count; this method will not.

Implementation

MssqlProjectedQuery<(TRow, Map<String, Object?>)> withAggregates(
  List<MssqlRelationAggregate<TRow>> Function(TFields fields) aggregates,
) {
  final specs = aggregates(fields);
  if (specs.isEmpty) {
    throw ArgumentError.value(
      specs,
      'aggregates',
      'withAggregates needs at least one relation aggregate.',
    );
  }
  final seen = <String>{};
  final extras = <MssqlProjectionColumn>[];
  for (final spec in specs) {
    final folded = spec.alias.toLowerCase();
    if (!seen.add(folded)) {
      throw ArgumentError.value(
        spec.alias,
        'aggregates',
        'withAggregates has two columns named "${spec.alias}".',
      );
    }
    extras.add(
      MssqlProjectionColumn(
        spec.alias,
        MssqlScalarSubquery(
          _existsQuery(spec.relation, projection: spec.expression),
        ),
      ),
    );
  }
  return select(
    MssqlProjection<(TRow, Map<String, Object?>)>(
      name: 'withAggregates',
      columns: <MssqlProjectionColumn>[
        for (final column in binding.columns)
          MssqlProjectionColumn(column.name, Col(column.name)),
        ...extras,
      ],
      map: (row) {
        final values = <String, Object?>{};
        for (var i = 0; i < extras.length; i++) {
          values[extras[i].alias] = row.at(binding.columns.length + i);
        }
        return (binding.fromRow(row), values);
      },
    ),
  );
}