withCount method

@override QueryBuilderInterface<T> withCount(dynamic relations)

Load relationship counts without loading the full relationships.

Adds a {relation}Count attribute to each model with the count.

final users = await User.query()
  .withCount(['posts', 'comments'])
  .get();

print(users.first.postsCount); // 25
print(users.first.commentsCount); // 150

You can also apply constraints to the count:

final users = await User.query()
  .withCount({
    'posts': (q) => q.where('published', '=', true),
    'comments': (q) => q.where('approved', '=', true),
  })
  .get();

Implementation

@override
QueryBuilderInterface<T> withCount(dynamic relations) {
  if (modelFactory == null) {
    throw Exception('withCount requires a model factory');
  }
  final model = modelFactory!({});
  if (model is! HasRelations) {
    throw Exception('Model does not use HasRelations trait');
  }

  final Map<String, void Function(QueryBuilderInterface<dynamic>)>
      normalized = {};

  if (relations is String) {
    normalized[relations] = (q) {};
  } else if (relations is List) {
    for (final item in relations) {
      if (item is String) {
        normalized[item] = (q) {};
      }
    }
  } else if (relations is Map) {
    relations.forEach((key, value) {
      if (value is void Function(QueryBuilderInterface<dynamic>)) {
        normalized[key] = value;
      }
    });
  }

  normalized.forEach((relationName, callback) {
    final relationObj = (model as HasRelations).relation(relationName);
    final relationQuery = relationObj.getRelationExistenceQuery(
      relationObj.getQuery(),
      this,
    );

    callback(relationQuery);

    if (relationQuery is QueryBuilder) {
      relationQuery._columns = [];
      // Prevent nested withCount from adding extra columns to this scalar subquery
      relationQuery._defaultWithCountApplied = true;
    }

    relationQuery.selectRaw('COUNT(*)');
    selectSub(relationQuery, '${relationName}_count');
  });

  return this;
}