You Need A Strongly Typed Query Builder
blogjoys-of-apex
Posted: October 15, 2021

You Need A Strongly Typed Query Builder

Table of Contents:

  • Building A Basic Use-Case

  • Aggregate Queries With Strongly Typed SOQL

    • Introducing The Aggregate Object
    • Introducing The AggregateRepository
    • Incorporating Strongly Typed Aggregate Queries Into The Mocking Framework
    • One Last Step: Decorating Aggregate Results

  • Wrapping Up: Strongly Typed Queries

Structured Query Language (SQL) and the basic Salesforce equivalent, SOQL, share many commanalities. Each has the ability to pull data back from the database through the use of specialized keywords. SOQL has many peculiarities, not the least of which are the limits the aspiring developer is under when using it. What may seem like one of the best features of Apex — its seamless integration with SOQL — is, however, unusable when it comes to producing truly dynamic and flexible queries. As well, using SOQL of any kind exposes the code you’re writing to an explicit dependency — the database. If you’ve read about mocking DML, you know that having such a dependency makes testing hard; if our tests are to serve as the source of truth for whether or not our code works, being able to test things easily becomes a much bigger priority in our lives. We want to move fast and confidently when writing code, but SOQL stands in the way of that! In order to write meaningful tests when our classes depend on database information, we need the ability to both interact with queries, and with the results of those queries. We need a strongly typed query builder!

It’s been many moons since I first wrote about the Repository pattern, but I think it’s worth revisiting at this juncture. I’ve also recently been writing a lot of dynamic SOQL — which always gets me thinking about just how much better the use of this pattern is (for more on this, see Strongly Typed Parent & Child Queries). Let’s review where we ended up in terms of syntax:

// the vanilla Apex SOQL version
List<Account> accounts = [SELECT Id, Name, AnnualRevenue FROM Account];
// the vanilla Apex dynamic SOQL version
List<Account> accounts = Database.query('SELECT Id, Name, AnnualRevenue FROM Account');

// the Repository version
Query accountQuery = new Query(Account.Id, Query.Operator.NOT_EQUALS, new List<Id>());
// a slightly more nuanced version
Query accountQuery = Query.notEquals(Account.Id, new List<Id>());

For a basic “pull back everything from the database” query, the benefit of having a strongly typed version of the query may not appear immediately obvious. And, to be clear, the shown Query object is actually adding a where clause; where perhaps it might be more beneficial to have a getAll() method directly on our Repository object. Let me show you the interface for that object to refresh your memory:

public interface IRepository {
  List<SObject> get(Query query);
  List<SObject> get(List<Query> queries);
}

It’s trivial to add:

public interface IRepository {
  List<SObject> get(Query query);
  List<SObject> get(List<Query> queries);
  List<SObject> getAll();
}

But — most queries have where clauses. Most queries are trying to filter data. That’s where we’ll put the focus in this article; to show off the advantages to a strongly typed version of queries, let’s explore a basic use-case.

Building A Basic Use-Case

Let’s say you’re working with some complicated SOQL, and you’re finding it difficult to test all the different aspects of the where clause:

public without sharing class OpportunityUpdater {
  public void doSomething(List<Opportunity> updatedOpps) {
    Set<Id> accountIds = this.fillIds(Opportunity.AccountId, updatedOpps);

    Map<Id, Account> idToAccount = new Map<Id, Account>([
      SELECT Id
      FROM Account
      WHERE Id = :accountIds
      AND (
        Industry IN ('First', 'Second', 'Third') OR
        NumberOfEmployees > 100
      )
    ]);

    for (Opportunity opp : updatedOpps) {
      if (idToAccount.containsKey(opp.AccountId)) {
        // do something specific to this special subset
        // of opportunities
      }
    }
  }

  private Set<Id> fillIds(SObjectField fieldToken, List<SObject> records) {
    Set<Id> recordIds = new Set<Id>();
    for (SObject record : records) {
      recordIds.add((Id) record.get(fieldToken));
    }
    return recordIds;
  }
}

Ahh yes, the classic OpportunityUpdater. This example object gets a lot of use! 😅 The query used to fetch accounts that will end up marking the matching opportunities in some special business way is by no means that complicated — but look at how the addition of a “simple” or clause has created a wealth of testing combinations:

  • Accounts with an Industry of First and NumberOfEmployees less than 100
  • Accounts with an Industry of Second and NumberOfEmployees less than 100
  • Accounts with an Industry of Third and NumberOfEmployees less than 100
  • Accounts with a different industry and NumberOfEmployees greater than 100
  • (Consolidating this one) - Accounts with an Industry of First/Second/Third and NumberOfEmployees greater than 100

So that “simple” or clause has actually introduced seven different testing scenarios. Yikes. But we know with the power of strongly typed queries, this should be relatively easy. We’ll just pass in a list of queries to our Repository object and …

// wait a second
List<Query> accountQueries = new List<Query>{
  Query.equals(
    Account.Id,
    accountIds
  ),
  Query.equals(
    Account.Industry,
    new List<String>{ 'First', 'Second', 'Third' }
  ),
  Query.greaterThan(
    Account.NumberOfEmployees,
    100
  )
};

Hmm. Well. OK! It turns out we didn’t cover the creation of queries using “or” in the original post. It is, however, something that I added in after the fact to the Github repository after the fact. Here’s what the test looks like:

@IsTest
static void shouldProduceOrStatements() {
  Id nullId = null;
  String expectedQuery = '(Id = null OR Id != null)';

  // or is a reserved word in Apex, so the method has to be "orQuery"
  Query orQuery = Query.orQuery(
    Query.equals(Account.Id, nullId),
    Query.notEquals(Account.Id, nullId)
  );

  System.assertEquals(expectedQuery, orQuery.toString());
}

Which means that our list of Query objects ends up looking like this:

List<Query> accountQueries = new List<Query>{
  Query.equals(
    Account.Id,
    accountIds
  ),
  Query.orQuery(
    Query.equals(
      Account.Industry,
      new List<String>{ 'First', 'Second', 'Third' }
    ),
    Query.greaterThan(
      Account.NumberOfEmployees,
      100
    )
  )
};

The beauty with this system is that by the time the accountQueries list makes it to our Repository object, the Repository has no idea what sort of filters that list of queries is putting into place on the accounts that get returned. It also doesn’t know, for example, that one of the query objects in the list is actually comprised of two other queries. When properly encapsulated objects meet, beautiful things happen.

If you employ the Factory pattern to create the OpportunityUpdater, your tests can remain blissfully decoupled from your production level code:

public without sharing class OpportunityUpdater {
  IRepository accountRepo;
  public OpportunityUpdater(Factory factory) {
    this.accountRepo = factory.repoFactory.getAccountRepo();
  }

  public void doSomething(List<Opportunity> updatedOpps) {
    // ...
  }
}

// and in the factory ...

public class Factory {
  public OpportunityUpdater getOppUpdater() {
    return new OpportunityUpdater(this);
  }
}

// and then in your tests:
@IsTest
private class OpportunityUpdaterTests {
  @TestSetup
  static void setup() {
    insert new Account(Name = OpportunityUpdaterTests.class.getName());
  }
  @IsTest
  static void shouldUpdateOpportunitiesWithFirstIndustry() {
    // arrange
    Account acc = [SELECT Id FROM Account];
    acc.Industry = 'First';
    RepoFactoryMock.QueryResults.add(acc);
    Opportunity opp = new Opportunity(AccountId = acc.Id);

    // act
    Factory.getFactory().withMocks.getOppUpdater().doSomething(
      new List<Opportunity>{ opp }
    );

    // assert
    System.assertEquals(
      'Expected Value',
      opp.FieldUpdatedInDoSomething__c,
      'Industry matches so opp should have been updated!'
    );
    System.assertEquals(
      'SELECT Id FROM Account WHERE Id IN (\'' + acc.Id + '\')\n' +
      'AND (Industry IN (\'First\', \'Second\', \'Third\') OR NumberOfEmployees > 100)',
      RepoFactoryMock.QueriesMade[0];
    );
  }
}

Things to note about the RepoFactoryMock object:

// in RepoFactoryMock.cls
@TestVisible
private static List<SObject> QueryResults = new List<SObject>();
@TestVisible
private static List<Query> QueriesMade = new List<Query>();
  • those two objects serve different purposes. QueryResults is used in the “arrange” part of the test to add in an actual Account. If the object under test — in this case, the OpportunityUpdater has an account repository attached to it, and if a query is performed for that repository, any “matches” by SObjectType get returned to the calling code. Depending on your production-level code, you might or might not need to perform DML on the associated object; note that in this case, while the Account that gets added to QueryResults actually exists, the Industry field only gets written in memory; it’s not necessary to perform additional DML to update the Account
  • the QueriesMade list can be indexed into by any test method to validate that all query conditions have been properly added. I can’t stress enough how huge this is; typically we would have to perform the aforementioned seven different combinations of Account Industry and Number Of Employees — you can still do that, to be clear. However, if you’re able to validate that the query has the appropriate conditions in place, you don’t necessarily have to test every combination — if one works, they should all work, in other words

Even without the Factory object, you can still opt into the use of the RepoFactoryMock (though perhaps at that point it could just be called the RepoMock!):

public without sharing class OpportunityUpdater {
  IRepository accountRepo;
  public OpportunityUpdater(IRepository accountRepo) {
    this.accountRepo = accountRepo;
  }
  // etc ..
}

// and then in the test:
@IsTest
static void shouldUpdateOpportunitiesWithFirstIndustry() {
  // arrange
  Account acc = [SELECT Id FROM Account];
  acc.Industry = 'First';
  update acc;
  Opportunity opp = new Opportunity(AccountId = acc.Id);

  // act
  new OpportunityUpdater(new RepoMock()).doSomething(
    new List<Opportunity>{ opp }
  );

  // assert
  System.assertEquals(
    'Expected Value',
    opp.FieldUpdatedInDoSomething__c,
    'Industry matches so opp should have been updated!'
  );
  System.assertEquals(
    'SELECT Id FROM Account WHERE Id IN (\'' + acc.Id + '\')\n' +
    'AND (Industry IN (\'First\', \'Second\', \'Third\') OR NumberOfEmployees > 100)',
    RepoMock.QueriesMade[0];
  );
}

I’ve never been crazy about this style of dependency injection; it’s fine with one dependency, but if you need to use mock objects for several dependencies, it can quickly get out of hand. I also tend to not use the Stub API in Apex because it doesn’t fix the injection problem — you still need to spin up your Stub API mock and pass that object along into the object you’re testing’s constructor. You also now have to mock out what you want the expected response to be; this sounds like a lot of work to me, especially for something like queries (which have a well-understood pattern of what should be returned; I like the Stub API for many things, this just doesn’t happen to be one of them).

That being said, I offer it up as an alternative — embracing the Factory pattern means committing to its usage, and while I firmly believe that its usage comes with massive benefits in terms of reduced testing time (both in terms of speeding up how fast tests actually run, and how quickly you or your team can write individual tests), as well as proper abstraction and encapsulation, I recognize that getting buy-in for the Factory isn’t always easy.

Returning to the matter at hand, you’ve now seen how a strongly typed query builder carries with it many attractive benefits:

  • the ability to validate queries that have been performed
  • the ability to easily mix & match between records that actually exist, records that exist but have been updated in-memory with values that conform to what you need to test, and records that are purely mocked within the confines of an individual test method
  • the ability to properly encapsulate how queries are being performed

Let’s take it a step further — let’s look at what it would take to perform aggregate queries with a strongly typed query builder.

Aggregate Queries With Strongly Typed SOQL

Aggregate queries in SOQL already suffer from an unfortunate coupling effect — you either live with expr0 and expr1 etc to access the results of your aggregates, or you rely upon an alias whose name can’t be abstracted out of the query itself. Let’s see if we can do a little bit better than that by using strongly typed objects instead.

In the same way that “or” queries are made using the Query object without it being necessary for the Repository you end up using to perform your queries knowing that there’s anything special about the object it’s being passed, we can use polymorphism to accomplish something similar within the Repository itself, starting with the interface:

public interface IRepository extends IDML {
  List<SObject> getAll();
  List<SObject> get(Query query);
  List<SObject> get(List<Query> queries);
}

// and introducing:
public interface IAggregateRepository extends IRepository {
  void groupBy(Schema.SObjectField fieldToken);
  void groupBy(String fieldName);

  // always a fun one
  Integer count();
  Integer count(Query query);
  Integer count(List<Query> queries);
  // with support for sum, count, count distinct, average, max, min
  List<AggregateResult> aggregate(Aggregation aggregation);
  List<AggregateResult> aggregate(Aggregation aggregation, Query query);
  List<AggregateResult> aggregate(Aggregation aggregation, List<Query> queries);
  List<AggregateResult> aggregate(List<Aggregation> aggregations);
  List<AggregateResult> aggregate(List<Aggregation> aggregations, Query query);
  List<AggregateResult> aggregate(List<Aggregation> aggregations, List<Query> queries);
}

I hope you’re looking at this interface in pleasant surprise. It could be much larger, but by introducing a new object, Aggregation (which we’ll do in true TDD fashion in a moment), the complexity that might have come out of implementing aggregate queries method by method is much reduced. And, in reality, the changes that we need to make to introduce an AggregateRepository are minimal beyond this step. Let’s get to work!

Introducing The Aggregate Object

Let’s start with our tests that drive out how we’d like our Aggregation object to function:

@IsTest
private class AggregationTests {
  @IsTest
  static void shouldCorrectlyFormatSum() {
    // like the Query object, it'll probably make more
    // sense to expose the aggregate functions as static builders -
    // but since we're starting from nothing, let's stub this out
    String expectedAlias = 'myAlias';
    Aggregation agg = new Aggregation(Aggregation.Operation.SUM, Opportunity.Amount, expectedAlias);
    System.assertEquals('SUM(Amount) myAlias', agg.toString());
    System.assertEquals(expectedAlias, agg.getAlias());
    System.assertEquals('Amount', agg.getFieldName());
  }
}

Right away, it feels like we’ll need an Operation inner enum in the Aggregation class; we’ll also want a way to retrieve the used alias so that we can properly access computed values after the fact.

public class Aggregation {
  // we can reduce the visibility of this enum later
  public enum Operation {
    COUNT,
    COUNT_DISTINCT,
    SUM,
    AVERAGE,
    MAX,
    MIN
  }

  private final Operation op;
  private final String fieldName;
  private final String alias;

  public Aggregation(Operation op, Schema.SObjectField fieldToken, String alias) {
    this.op = op;
    this.fieldName = fieldToken.getDescribe().getName();
    this.alias = alias;
  }

  public String getAlias() {
    return this.alias;
  }

  public String getFieldName() {
    return this.fieldName;
  }

  public override String toString() {
    return this.op.name() + '(' + fieldName + ') ' + this.alias;
  }
}

One test green — before things get out of hand, let’s refactor how the Aggregation object gets constructed:

public class Aggregation {
  private enum Operation {
    COUNT,
    COUNT_DISTINCT,
    SUM,
    AVERAGE,
    MAX,
    MIN
  }

  private final Operation op;
  private final String fieldName;
  private final String alias;

  private Aggregation(Operation op, Schema.SObjectField fieldToken, String alias) {
    this.op = op;
    this.fieldName = fieldToken.getDescribe().getName();
    this.alias = alias;
  }

  public static Aggregation sum(Schema.SObjectField fieldToken, String alias) {
    return new Aggregation(Operation.SUM, fieldToken, alias);
  }

  public static Aggregation count(Schema.SObjectField fieldToken, String alias) {
    return new Aggregation(Operation.COUNT, fieldToken, alias);
  }

  public static Aggregation countDistinct(Schema.SObjectfield fieldToken, String alias) {
    return new Aggregation(Operation.COUNT_DISTINCT, fieldToken, alias);
  }

  public static Aggregation average(Schema.SObjectfield fieldToken, String alias) {
    return new Aggregation(Operation.AVERAGE, fieldToken, alias);
  }

  public static Aggregation max(Schema.SObjectfield fieldToken, String alias) {
    return new Aggregation(Operation.MAX, fieldToken, alias);
  }

  public static Aggregation min(Schema.SObjectfield fieldToken, String alias) {
    return new Aggregation(Operation.MIN, fieldToken, alias);
  }
  // etc ...
}

Now our test looks like:

// in AggregationTests.cls
@IsTest
static void shouldCorrectlyFormatSum() {
  String expectedAlias = 'myAlias';
  Aggregation agg = Aggregation.sum(Opportunity.Amount, expectedAlias);
  System.assertEquals('SUM(Amount) myAlias', agg.toString());
  System.assertEquals(expectedAlias, agg.getAlias());
}

// we'll do one more test ... but hopefully you get it.
// they're pretty simple

@IsTest
static void shouldCorrectlyFormatCountDistinct() {
  String expectedAlias = 'myAlias';
  Aggregation agg = Aggregation.countDistinct(Opportunity.StageName, expectedAlias);
  System.assertEquals('COUNT_DISTINCT(StageName) myAlias', agg.toString());
  System.assertEquals(expectedAlias, agg.getAlias());
}

Now that we have our strongly typed Aggregation object, we’re ready to feed that into an implementation for the IAggregateRepository.

Introducing The AggregateRepository

Given that all queries end up being parsed down into basic SOQL, you might pose the question: “Why bother introducing an additional interface for behavior associated with SOQL?” It’s tempting to stuff classes with additional responsibilities, but the Single Responsibility Principle demands that we resist that urge. By siloing behavior — even related behavior — into individual classes, we’re giving ourselves a much more targeted surface area for debugging; we reduce the complexity associated with understanding how individual objects interact with one another; we raise the visibility of when our code needs to consume aggregate queries versus non-aggregate queries. Because the AggregateRepository will subclass the Repository implementation, consumers that don’t need access to aggregate queries can store Repository dependencies as IRepository instances; consumers that do need access to aggregate queries can store those dependencies as IAggregateRepository instances.

Let’s dive into the tests for the class we’d like to see:

@IsTest
private class AggregateRepositoryTests {
  @IsTest
  static void shouldAggregateSum() {
    Account parent = new Account(Name = AggregateRepositoryTests.class.getName());
    Account secondParent = new Account(Name = 'Second parent');
    insert new List<Account>{ parent, secondParent };

    Opportunity opp = new Opportunity(
      Name = 'opp',
      Amount = 1,
      AccountId = parent.Id,
      StageName = 'sum',
      CloseDate = System.today()
    );
    Opportunity secondOpp = new Opportunity(
      Name = 'opp2',
      Amount = 1,
      AccountId = secondParent.Id,
      StageName = 'sum',
      CloseDate = System.today()
    );
    Opportunity anotherSecondParentMatch = new Opportunity(
      Name = 'opp3',
      Amount = 1,
      AccountId = secondParent.Id,
      StageName = 'sum',
      CloseDate = System.today()
    );
    insert new List<Opportunity>{ opp, secondOpp, anotherSecondParentMatch };

    Aggregation sum = Aggregation.sum(Opportunity.Amount, 'oppSum');
    IAggregateRepository repo = new AggregateRepository(
      Opportunity.SObjectType,
      new List<SObjectField>{ Opportunity.AccountId, Opportunity.Id, Opportunity.Amount },
      new RepoFactory() // see the Repository post for more!
    );
    repo.groupBy(Opportunity.AccountId);
    List<AggregateResult> results = repo.aggregate(sum);

    System.assertEquals(2, results?.size());
    for (AggregateResult res : results) {
      if (res.get('AccountId') == secondParent.Id) {
        System.assertEquals(2, res.get(sum.getAlias()));
      } else {
        System.assertEquals(1, res.get(sum.getAlias()));
      }
    }
  }
}

It’s not the most beautiful test in the world. In general, we’ll be looking to incorporate the AggregateRepository into the existing RepositoryMock framework so that AggregateResult instances can be mocked the same as regular SObject queries, but first we need to prove that the AggregateRepository works. Principally, we’ll be looking to show:

  • aggregations are returned correctly
  • regardless of the List<SObjectField> tokens supplied to the repository, only grouped by fields are returned in the select statement of a query
  • the alias is correctly used

Once that functionality can be shown to work, we don’t have to go through the process in every test of performing loads of DML, setting up SObjects adjacent to one another; in other words, for downstream consumers of the AggregateRepository, we’ll be able to utilize mocking to speed up test writing and test peformance, with the added benefit of increasing confidence and iteration speed as a result.

Here’s the barebones AggregateRepository implementation (note that we have to stub out quite a few methods, but that most of them are overloads):

public inherited sharing class AggregateRepository extends Repository implements IAggregateRepository {
  public AggregateRepository(Schema.SObjectType repoType, List<Schema.SObjectField> queryFields, RepoFactory repoFactory) {
    super(repoType, queryFields, repoFactory);
  }

  public void groupBy(Schema.SObjectField fieldToken) {
    this.groupBy(fieldToken.getDescribe().getName());
  }
  public void groupBy(String fieldName) {
  }

  public Integer count() {
    return this.count(new List<Query>());
  }
  public Integer count(Query query) {
    return this.count(new List<Query>{ query });
  }
  public Integer count(List<Query> queries) {
    return null;
  }

  public List<AggregateResult> aggregate(Aggregation aggregation) {
    return this.aggregate(new List<Aggregation>{ aggregation}, new List<Query>());
  }
  public List<AggregateResult> aggregate(Aggregation aggregation, Query query) {
    return this.aggregate(new List<Aggregation>{ aggregation}, new List<Query>{ query });
  }
  public List<AggregateResult> aggregate(Aggregation aggregation, List<Query> queries) {
    return this.aggregate(new List<Aggregation>{ aggregation}, queries);
  }
  public List<AggregateResult> aggregate(List<Aggregation> aggregations) {
    return this.aggregate(aggregations, new List<Query>());
  }
  public List<AggregateResult> aggregate(List<Aggregation> aggregations, Query query) {
    return this.aggregate(aggregations, new List<Query>{ query });
  }
  public virtual List<AggregateResult> aggregate(List<Aggregation> aggregations, List<Query> queries) {
    return null;
  }
}

Now we can run our test … which fails, of course, with System.AssertException: Assertion Failed: Expected: 2, Actual: null. TDD is a beautiful thing — we now have carte blanche to go in and start implementing. As well, since most of the methods are overloaded to begin with, by getting this one test passing, we’ll actually be getting all the AggregateResult methods passing 🍾:

# In Repository.cls
public virtual List<SObject> get(List<Query> queries) {
- String finalQuery = this.getSelectAndFrom() + this.addWheres(queries);
+ String finalQuery = this.getFinalQuery(queries);
  return this.performQuery(finalQuery);
}

+ protected virtual String getFinalQuery(List<Query queries>) {
+  return this.getSelectAndFrom() + this.addWheres(queries);
+}

private String getSelectAndFrom() {
- return 'SELECT ' + this.addSelectFields() + '\nFROM ' + this.repoType;
+ return 'SELECT ' + String.join(new List<String>(this.addSelectFields()), ', ')+ '\nFROM ' + this.repoType;
}

- private String addSelectFields() {
+ protected virtual Set<String> addSelectFields() {
  Set<String> fieldStrings = new Set<String>{ 'Id' };
  for (SObjectField field : this.queryFields) {
    fieldStrings.add(field.getDescribe().getName());
  }
-  return String.join(new List<String>(fieldStrings), ', ');
+  return fieldStrings;
}

These small changes will help support our “only grouped by fields are returned in the select statement of a query” bullet point. On to AggregateRepository:

// in AggregateRepository.cls
private final Set<String> groupedByFieldNames;
private List<Aggregation> aggregations;
public AggregateRepository(Schema.SObjectType repoType, List<Schema.SObjectField> queryFields, RepoFactory repoFactory) {
  super(repoType, queryFields, repoFactory);
  this.groupedByFieldNames = new Set<String>();
}

public void groupBy(Schema.SObjectField fieldToken) {
  this.groupBy(fieldToken.getDescribe().getName());
}
public void groupBy(String fieldName) {
  this.groupedByFieldNames.add(fieldName);
}

// ... etc

public virtual List<AggregateResult> aggregate(List<Aggregation> aggregations, List<Query> queries) {
  this.aggregations = aggregations;
  return (List<AggregateResult>) this.get(queries);
}

protected override Set<String> addSelectFields() {
  Set<String> baseFields = new Set<String>();
  Boolean aggregatesIdField = false;
  for (Aggregation agg : aggregations) {
    baseFields.add(agg.toString());
  }
  baseFields.addAll(this.groupedByFieldNames);
  return baseFields;
}

protected override String getFinalQuery(List<Query> queries) {
  String baseString = super.getFinalQuery(queries);
  return this.groupedByFieldNames.isEmpty() ? baseString : baseString + '\nGROUP BY ' + String.join(new List<String>(this.groupedByFieldNames), ',');
}

Alakazam! Just like that, our test passes. It seems the majority of our journey has come to pass (spoilers: anytime you’re considering writing a sentence like this, keep in mind that an unexpected roadblock will soon present itself 😄). As in most things with programming, we get 80% of the functionality with 20% of the effort. Just to show how amazing our newfound AggregateQueryBuilder is (before implementing the basic COUNT() method), let’s examine an even more basic query, something like:

SELECT COUNT(Amount) wowza
FROM Opportunity

No where clause, for an added twist, but behavior that differs subtly from what might get returned from the still-not-implemented COUNT() method — in SOQL, using COUNT(fieldName) only returns non-null results for that field:

// in AggregateRepositoryTests.cls
@IsTest
static void shouldReturnCountOnFieldNameCorrectly() {
  insert new List<Opportunity> {
    new Opportunity(
      Name = 'opp',
      StageName = 'sum',
      CloseDate = System.today()
    ),
    new Opportunity(
      Name = 'opp2',
      Amount = 1,
      StageName = 'sum',
      CloseDate = System.today()
    )
  };

  IAggregateRepository repo = new AggregateRepository(
    Opportunity.SObjectType,
    new List<SObjectField>{ Opportunity.AccountId, Opportunity.Id, Opportunity.Amount },
    new RepoFactory()
  );
  Aggregation countOfAmount = Aggregation.count(Opportunity.Amount, 'wowza');
  List<AggregateResult> results = repo.aggregate(countOfAmount);

  System.assertEquals(1, results.size());
  System.assertEquals(1, results[0].get(countOfAmount.getAlias()));
}

Let’s add in the last of the needed functionality, for supporting COUNT() queries:

// in AggregateRepositoryTests.cls
@IsTest
static void shouldReturnCountAsInteger() {
  insert new List<Opportunity> {
    new Opportunity(
      Name = 'opp',
      StageName = 'sum',
      CloseDate = System.today()
    ),
    new Opportunity(
      Name = 'opp2',
      Amount = 1,
      StageName = 'sum',
      CloseDate = System.today()
    )
  };
  IAggregateRepository repo = new AggregateRepository(
    Opportunity.SObjectType,
    new List<SObjectField>{ Opportunity.AccountId, Opportunity.Id, Opportunity.Amount },
    new RepoFactory()
  );

  System.assertEquals(2, repo.count())
}

// in AggregateRepository.cls
public inherited sharing class AggregateRepository extends Repository implements IAggregateRepository {
  private final Set<String> groupedByFieldNames;
  private List<Aggregation> aggregations;
  private Boolean isNumberCountQuery = false;

  // etc ...

  public Integer count(List<Query> queries) {
    this.isNumberCountQuery = true;
    Integer recordCount = Database.countQuery(this.getFinalQuery(queries));
    this.isNumberCountQuery = false;
    return recordCount;
  }

  protected override Set<String> addSelectFields() {
    Set<String> baseFields = new Set<String>();
    if (this.isNumberCountQuery) {
      baseFields.add('COUNT()');
      return baseFields;
    }
    // etc ...
  }
}

The syntax for performing these tests is smooth; it feels good to be using objects to encapsulate our aggregate queries, and there’s very little in the way of added boilerplate or ceremony. However — we’re still inserting records. We’re still doing DML. For these tests, that’s an important part of the process — we have to prove that everything works. With that being said, I’ll spare the rest of the test methods driving out the other aggregate methods now that we’ve demonstrated a basic aggregation and COUNT() working.

It’s taken some time to write out and show the underpinnings behind how the AggregateRepository works, but at its core, we’ve really only introduced two new objects (which took minimal time to implement), and an interface. The actually exciting part of this process is what it looks like to end up taking the AggregateRepository into use as a dependency, and how we can then mock out aggregated results when needed within our tests to prevent huge amounts of test setup and database-level interactions. It’s time for us to take this to the next level!

Incorporating Strongly Typed Aggregate Queries Into The Mocking Framework

We’re very nearly there, where “there” is easy to write and easy to run tests when aggregate queries are needed, coupled with the power of strongly typed queries in your production-level code. Let’s return to the RepoFactory from the Repository Pattern post:

public virtual class RepoFactory {
  // previously this would have been IRepository for the type signature
  public virtual IAggregateRepository getOppRepo() {
    List<SObjectField> queryFields = new List<SObjectField>{
      Opportunity.IsWon,
      Opportunity.StageName
      // etc ...
    };
    return new AggregateRepository(Opportunity.SObjectType, queryFields, this);
  }

  // etc ...

  public virtual IDML getDML() {
    return new DML();
  }
}

The RepoFactoryMock stores overrides just for tests, like such:

public class RepoFactoryMock extends RepoFactory {
  @TestVisible
  private static List<SObject> QueryResults = new List<SObject>();
  @TestVisible
  private static List<Query> QueriesMade = new List<Query>();

  // we're about to update this line to reflect the type signature
  // now being IAggregateRepository
  public override IRepository getOppLineItemRepo() {
    List<SObject> queriedResults = this.getResults(OpportunityLineItem.SObjectType);
    return queriedResults.size() > 0 ? new RepoMock(queriedResults, this) : super.getOppLineItemRepo();
  }

  // etc ...
}

Because AggregateResult objects are a subclass of SObject, we don’t need to change the QueryResults member variable within RepoFactory — indeed, we end up changing very little within the outer mock class (beyond adding a List<Aggregation> static variable to track during tests):

+@TestVisible
+private static List<Aggregation> AggregatesMade = new List<Aggregation>();

-public override IRepository getOppLineItemRepo() {
+public override IAggregateRepository getOppLineItemRepo() {
}

After changing the type signature for the getOppLineItemRepo method, the RepoMock inner class needs to be adjusted slightly, as well:

// in RepoFactoryMock.cls, as an inner class:
@TestVisible
- private class RepoMock extends Repository {
+ private class RepoMock extends AggregateRepository
  private final List<SObject> results;

  @TestVisible
  private RepoMock() {
    this(new List<SObject>(), new RepoFactoryMock());
  }

  public RepoMock(List<SObject> results, RepoFactory factory) {
    super(factory);
    this.results = results;
  }

  public override List<SObject> getAll() {
    return this.get(new List<Query>());
  }

  public override List<SObject> get(Query query) {
    return this.get(new List<Query>{ query });
  }

  public override List<SObject> get(List<Query> queries) {
    QueriesMade.addAll(queries);
    return this.results;
  }
+
+ public override List<AggregateResult> aggregate(List<Aggregation> aggregations, List<Query> queries) {
+   AggregatesMade.addAll(aggregations);
+   return (List<AggregateResult>) this.get(queries);
+ }
}

And just like that, we’re in business. Now we can return to the OpportunityUpdater example from the Repository Pattern post to demonstrate the ease with which being able to mock aggregate results in tests can be (and the expressiveness of the production-level code)!

Let’s say whenever an opportunity is updated, we need to check for opportunities when the total amount for any line items’ TotalPrice fields exceeds $100,000. Here’s what OpportunityUpdater looks like so far, with just a stubbed out method so that we can call it in our tests:

public class OpportunityUpdater {
  private final IAggregateRepository oppLineItemRepo;

  public OpportunityUpdater(Factory factory) {
    this.oppLineItemRepo = factory.RepoFactory.getOppLineItemRepo();
  }

  // this is from the Repository pattern post
  public void updateOppsOnClose(List<Opportunity> updatedOpps) {
    Map<Id, Opportunity> idtoUpdatedOpps = new Map<Id, Opportunity>(updatedOpps);

    Query oppQuery = Query.equals(Opportunity.Id, idToUpdatedOpps.keySet());
    List<OpportunityLineItem> lineItems = (List<OpportunityLineItem>) this.oppLineItemRepo.get(oppQuery);
    for (OpportunityLineItem lineItem : lineItems) {
      if (lineItem.Description == 'business logic criteria') {
        Opportunity opp = idToUpdatedOpps.get(lineItem.OpportunityId);
        opp.Description = 'Magic Business String';
      }
    }
  }

  public void checkForOppsOverSixFigures(List<Opportunity> updatedOpps) {
    // uh oh, we'd better implement this
  }
}

And on to the main event: setting up tests our tests with a mocked AggregateResult:

// this class was still written in the old snake case naming style
// so I kept it consistent
@IsTest
static void it_should_update_opps_with_line_items_over_six_figures() {
  String expectedAlias = 'sumOli';
  Opportunity firstOpp = new Opportunity(Id = TestingUtils.generateId(Opportunity.SObjectType));
  Opportunity secondOpp = new Opportunity(Id = TestingUtils.generateId(Opportunity.SObjectType));

  AggregateResult fakeAgg = createAggregateResult(
    new Map<String, Object>{
      'OpportunityId' => firstOpp.Id,
      expectedAlias => 100004
    }
  );
  RepoFactoryMock.QueryResults.add(fakeAgg);

  Factory.getFactory().withMocks.getOpportunityUpdater().checkForOppsOverSixFigures(new List<Opportunity>{ firstOpp, secondOpp });

  System.assertEquals('Six figure magic string', firstOpp.Description);
  System.assertNotEquals('Six figure magic string', secondOpp.Description);
  System.assertEquals(
    Aggregation.sum(OpportunityLineItem.TotalPrice, expectedAlias),
    RepoFactoryMock.AggregatesMade[0] // you'll need to override the "equals" method to do something like this (not shown)
  );
}

private static AggregateResult createAggregateResult(Map<String, Object> fieldNameToField) {
  JSONGenerator jsonGen = JSON.createGenerator(false);
  jsonGen.writeStartObject();
  for (String key : fieldNameToField.keySet()) {
    jsonGen.writeFieldName(key);
    jsonGen.writeObject(fieldNameToField.get(key));
  }
  jsonGen.writeEndObject();
  return (AggregateResult) JSON.deserialize(jsonGen.getAsString(), AggregateResult.class);
}

With the test failing, it’s now time to go implement our “logic” in OpportunityUpdater:

// in OpportunityUpdater.cls
public void checkForOppsOverSixFigures(List<Opportunity> updatedOpps) {
  Map<Id, Opportunity> idtoUpdatedOpps = new Map<Id, Opportunity>(updatedOpps);

  Query oppQuery = Query.equals(Opportunity.Id, idtoUpdatedOpps.keySet());
  Aggregation overSixFigures = Aggregation.sum(OpportunityLineItem.TotalPrice, 'sumOli');
  List<AggregateResult> results = this.oppLineItemRepo.aggregate(overSixFigures, oppQuery);

  for (AggregateResult res : results) {
    Decimal totalSalesPrice = (Decimal) res.get(overSixFigures.getAlias());
    if (totalSalesPrice > 100000) {
      Id oppId = (Id) res.get('OpportunityId');
      Opportunity opp = idtoUpdatedOpps.get(oppId);
      opp.Description = 'Six figure magic string';
    }
  }
}

I was writing this exact piece in a coffee shop two weeks ago — the end in sight, looking forward to sharing this piece with the world. It was at this exact moment that “the end”, definitionally, warped out of sight once more — because the test failed, even after implementing the logic in checkForOppsOverSixFigures. That wasn’t at all what I was anticipating, but in dismay I printed out to my terminal the output of the first object in the List<AggregateResults> results shown above:

System.AssertException: Assertion Failed: AggregateResult:{}

An empty object. Sacré bleu! No amount of trickery — and believe me, I tried — would result in anything other than this line. I paused my work for the day, intent on asking the Apex team about this in the morning. Sure enough, the bad news was conveyed to me: it’s not currently possible to mock some objects, and AggregateResult is on that list. While there is hope that this will be supported in the future (safe harbor), for now, introducing a Decorator object was recommended to me.

One Last Step: Decorating Aggregate Results

To create our decorator, we’ll introduce the AggregateRecord class (itself, an example of the DAO pattern; for more on that, see Mocking Apex History Records):

public interface IAggregateRepository extends IRepository {
  void groupBy(Schema.SObjectField fieldToken);
  void groupBy(String fieldName);

  // always a fun one
  Integer count();
  Integer count(Query query);
  Integer count(List<Query> queries);
  // with support for sum, count, count distinct, average, max, min
  List<AggregateRecord> aggregate(Aggregation aggregation);
  List<AggregateRecord> aggregate(Aggregation aggregation, Query query);
  List<AggregateRecord> aggregate(Aggregation aggregation, List<Query> queries);
  List<AggregateRecord> aggregate(List<Aggregation> aggregations);
  List<AggregateRecord> aggregate(List<Aggregation> aggregations, Query query);
  List<AggregateRecord> aggregate(List<Aggregation> aggregations, List<Query> queries);
}

public class AggregateRecord {
  private final Map<String, Object> keyToAggregateResult = new Map<String, Object>();

  public void putAll(Map<String, Object> values) {
    this.keyToAggregateResult.putAll(values);
  }

  public Object get(String key) {
    return this.keyToAggregateResult.get(key);
  }
}

// and then in AggregateRepository.cls
public virtual List<AggregateRecord> aggregate(List<Aggregation> aggregations, List<Query> queries) {
  this.aggregations = aggregations;
  List<AggregateResult> results = (List<AggregateResult>) this.get(queries);
  List<AggregateRecord> aggregateRecords = new List<AggregateRecord>();
  for (AggregateResult result : results) {
    AggregateRecord aggRecord = new AggregateRecord();
    aggRecord.putAll(result.getPopulatedFieldsAsMap());
    aggregateRecords.add(aggRecord);
  }
  return aggregateRecords;
}

And the very small diff from OpportunityUpdater:

public void checkForOppsOverSixFigures(List<Opportunity> updatedOpps) {
  Map<Id, Opportunity> idtoUpdatedOpps = new Map<Id, Opportunity>(updatedOpps);

  Query oppQuery = Query.equals(Opportunity.Id, idtoUpdatedOpps.keySet());
  Aggregation overSixFigures = Aggregation.sum(OpportunityLineItem.TotalPrice, 'sumOli');
-  List<AggregateResult> results = this.oppLineItemRepo.aggregate(overSixFigures, oppQuery);
+  List<AggregateRecord> results = this.oppLineItemRepo.aggregate(overSixFigures, oppQuery);

-  for (AggregateResult res : results) {
+  for (AggregateRecord res : results) {
    Decimal totalSalesPrice = (Decimal) res.get(overSixFigures.getAlias());
    if (totalSalesPrice > 100000) {
      Id oppId = (Id) res.get('OpportunityId');
      Opportunity opp = idtoUpdatedOpps.get(oppId);
      opp.Description = 'Six figure magic string';
    }
  }
}

This leaves us with slightly more in RepoFactoryMock (since we no longer have access to the underlying AggregateResult.SObjectType; this was the primary reason so little needed to change in this class to begin with, since an AggregateResult is also an instance of SObject) — but this is also a chance to clean-up the overrides for each repository that needs to be overridden:

public class RepoFactoryMock extends RepoFactory {
  @TestVisible
  private static Map<SObjectType, List<AggregateRecord>> AggregateResults = new Map<SObjectType, List<AggregateRecord>>();
  @TestVisible
  private static List<Aggregation> AggregatesMade = new List<Aggregation>();
  @TestVisible
  private static List<SObject> QueryResults = new List<SObject>();
  @TestVisible
  private static List<Query> QueriesMade = new List<Query>();

  public override IAggregateRepository getOppLineItemRepo() {
    // now this is super clean!
    return this.getRepoFromSObjectType(OpportunityLineItem.SObjectType, super.getOppLineItemRepo());
  }

  private IAggregateRepository getRepoFromSObjectType(SObjectType sObjectType, IAggregateRepository fallback) {
    IAggregateRepository repo;
    List<SObject> queriedResults = this.getResults(sObjectType);
    List<AggregateRecord> aggRecords = this.getAggregateResults(sObjectType);

    if (queriedResults.size() > 0) {
      repo = new RepoMock(queriedResults, this);
    } else if (aggRecords?.size() > 0) {
      repo = new RepoMock(aggRecords, this);
    } else {
      repo = fallback;
    }
    return repo;
  }

  private List<SObject> getResults(SObjectType sobjType) {
    List<SObject> resultList = new List<SObject>();
    for (SObject potentialResult : QueryResults) {
      if (potentialResult.getSObjectType() == sobjType) {
        resultList.add(potentialResult);
      }
    }
    return resultList;
  }

  private List<AggregateRecord> getAggregateResults(SObjectType sObjType) {
    return AggregateResults.get(sObjType);
  }

  @TestVisible
  private class RepoMock extends AggregateRepository {
    private final List<SObject> results;
    private final List<AggregateRecord> aggRecords;

    @TestVisible
    private RepoMock() {
      this(new List<SObject>(), new RepoFactoryMock());
    }

    public RepoMock(List<SObject> results, RepoFactory factory) {
      super(factory);
      this.results = results;
    }

    public RepoMock(List<AggregateRecord> records, RepoFactory factory) {
      super(factory);
      this.aggRecords = records;
    }

    public override List<SObject> getAll() {
      return this.get(new List<Query>());
    }

    public override List<SObject> get(Query query) {
      return this.get(new List<Query>{ query });
    }

    public override List<SObject> get(List<Query> queries) {
      QueriesMade.addAll(queries);
      return this.results;
    }

    public override List<AggregateRecord> aggregate(List<Aggregation> aggregations, List<Query> queries) {
      AggregatesMade.addAll(aggregations);
      QueriesMade.addAll(queries);
      return this.aggRecords;
    }
  }
}

Which brings us — finally — back to OpportunityUpdater_Tests:

@IsTest
static void it_should_update_opps_with_line_items_over_six_figures() {
  String expectedAlias = 'sumOli';
  Opportunity firstOpp = new Opportunity(Id = TestingUtils.generateId(Opportunity.SObjectType));
  Opportunity secondOpp = new Opportunity(Id = TestingUtils.generateId(Opportunity.SObjectType));

  AggregateRecord fakeAgg = new AggregateRecord();
  fakeAgg.putAll(new Map<String, Object>{ expectedAlias => 100001, 'OpportunityId' => firstOpp.Id });
  RepoFactoryMock.AggregateResults.put(OpportunityLineItem.SObjectType, new List<AggregateRecord>{ fakeAgg });

  Factory.getFactory().withMocks.getOpportunityUpdater().checkForOppsOverSixFigures(new List<Opportunity>{ firstOpp, secondOpp });

  System.assertEquals('Six figure magic string', firstOpp.Description);
  System.assertNotEquals('Six figure magic string', secondOpp.Description);
}

And now the test passes, no problem. Shifting to the AggregateRepositoryTests class (shown earlier in this post, but I won’t repeat it here), the only change necessary is updating the stored return result from calling the AggregateRepository from AggregateResult to our new AggregateRecord decorator — and the tests all pass.

Wrapping Up: Strongly Typed Queries

In the end, what have we seen?

  • using a strongly typed query builder gives you better access to type-safety, increasing development speed (through intellisense and reduced risk of typos) and code readability
  • combining that with the repository pattern allows you to easily mock while testing, which saves you from the hassle (and the long testing time) of needing to create many tiers of dependent records. You also get the added benefit of being able to validate that the queries fed into each repository are exactly what you expected. This is a great example of shift left testing — by validating what we expect to happen earlier in the process, we move faster and get better at proactively addressing issues.
  • aggregate queries themselves are tightly coupled to the aliases fed into the query / produced by default. Using a strongly typed query builder gives you better control flow over the alias associated with a given aggregation
  • combining that with the repository pattern allows you to easily mock aggregated results, as well as the queries that spawned them. Again, by shifting left the process of being able to test for and find bugs with our code, we lower our mean time to delivery and increase our speed.

Speaking a bit more on the subject of “shifting left” — imagine the scenario where you need to create an intense hierarchy of records purely in order to validate the results of the aggregate query. With normal testing practices, it’s easier to be tempted to limit your test to only one record — which might not expose bugs in a bulkified fashion. For example, my team recently caught an issue with some legacy code where the aggregated results could deviate between production-level transactions if items were slightly out of place in the List<AggregateResult>. Without going too much into the detail of how something like that happened, I’ll simply say that even knowing what the problem was, the process of reproducing the bug was difficult given how much test setup was necessary to recreate the correct hierarchy of records. With AggregateRepository, we could have fed in exactly what we were looking for (records slightly out of order), reproduced the issue, and had the bug fixed in hours. Instead, it took quite a bit longer than that.

In order to dive into the code shown here, please feel free to explore the Apex Mocks Stress Test repo, where I typically develop branches side-by-side with the posts I’m writing. The fully-fledged repository pattern is also available in a stand-alone Github repo. Note that the latter source of code is meant to serve as a template; it has been adopted and used by a variety of different organizations, and is meant to serve as a great starting point for adding strongly typed queries and the Factory and Repository patterns into your own codebases. For another example of how easy it is to extend the strongly typed query builder seen here, make sure not to miss out on Strongly Typed Parent & Child Queries!

In the past three years, hundreds of thousands of you have come to read & enjoy the Joys Of Apex. Over that time period, I've remained staunchly opposed to advertising on the site, but I've made a Patreon account in the event that you'd like to show your support there. Know that the content here will always remain free. Thanks again for reading — see you next time!