Remote Objects and Aggregate Function

Question

Is it possible to call count() with remote objects?

Answer 1

You can override the default remote object operations, programmatically get a count, add it to the result, and then use it in the Visualforce page.

In this case, to get the count you could override the retrieve method to get a count of whatever you need and store it in a property. In the Visualforce callback you can access the property in the event.result object. This isn’t an exact way to simulate the count() but may work depending on the use case.

Here’s an example that overrides the Account’s retrieve and adds in a count of the number of Accounts owned by the current user.

Override in the Visualforce

The retrieve method is overridden by supplying the retrieve attribute to the apex:remoteObjectModel.

<apex:remoteObjectModel 
    jsShortHand="acct" 
    name="Account" 
    fields="Id,Name,Phone,Fax"
    retrieve="{!$RemoteAction.AccountController.retrieveAccts}"/>

Override in the Apex Controller

public with sharing class AccountController {

    @RemoteAction
    public static Map<String, Object> retrieveAccts(
        String type, 
        List<String> fields, 
        Map<String, Object> criteria
    ) {

        // Retrieve using the standard retrieve
        Map<String, Object> result = RemoteObjectController.retrieve(type, fields, criteria);

        // Add in the total record count for the current user. Needed for pagination
        Integer numOwnedAccts = [
            SELECT Count() 
            FROM Account 
            WHERE OwnerId =: UserInfo.getUserId()
        ];

        // Create a new map since the result map is read-only
        Map<String, Object> customResult = 
            new Map<String, Object> {'totalRecordCount'=> numOwnedAccts};
        customResult.putAll(result);

        return customResult;
    }
}

Using the Visualforce Remote Object

This code creates criteria to support pagination and then calls retrieve. Note the line totalRecordCount = event.result.totalRecordCount. That’s what gets back the count.

// Set up criteria for pagination
var criteria = {
    limit: someLimitVal,
    orderby: [ {Name: "ASC NULLS LAST"} ]
};

// If offset is zero there will be an error
if (offsetVal >= 1) {
    criteria.offset = offsetVal;
}

var modelVar = new SObjectModel.acct();
modelVar.retrieve(criteria, function(err, records, event) {
    if (err) {
        console.log(err);
    } else {
        var totalRecordCount = event.result.totalRecordCount;
        records.forEach(function(record) {
            // do something
        });
    }
});

Unfortunately, you cannot get the non paged count of the criteria dynamically because the limit for limit is 100. For example, it would be useful (but not currently possible) to be able to do something like the following in the override.

Map<String, Object> countCriteria = new Map<String, Object>(criteria);
countCriteria.remove('offset');
countCriteria.put(‘limit’, 2000); 
Map<String, Object> countResult = RemoteObjectController.retrieve(type, fields, countCriteria);
Integer totalRecordCount = (Integer) countResult.get('size');

Answer 2

As far as I can tell from the documentation (and my own testing), there is currently no way to perform aggregate queries using Remote Objects.

The fields attribute of the apex:remoteObjectModel only accepts API names of the fields to be made available to your JavaScript and there is currently no option to provide aggregate functions.

A list of the object's fields to make accessible. Only these fields are available when existing objects are loaded from the server. The list is a comma-delimited string of the full API names of the fields.

In addition to this the Format and Options for Remote Objects Query Criteria page states that the retrieve function currently only supports the where, offset and limit criteria for queries.

Interestingly, the documentation states that there is no maximum for offset, unlike standard SOQL which has a limit of 2000 (documentation here).

Remote Objects uses an object to specify criteria for retrieve() operations. Use this object to specify where, limit, and offset conditions for your queries.

where Conditions

where conditions enable you to filter the results of a retrieve operation, much the same way that a WHERE condition in a SOQL query does. The operators that are available for where conditions are:

• eq: equals
• lt: less than
• gt: greater than
• like: string matching. As with SOQL, use “%” as a wildcard character.
• and: logical AND, used for combining conditions
• or: logical OR, used for combining conditions

limit and offset Conditions

limit and offset enable you to retrieve a specific number of records at a time and to page through an extended set of results.

Use limit to specify how many records to return in one batch of results. The default value is 20. The maximum is 100.

Use offset to specify how many records to skip in the overall result set before adding records to the returned results. The minimum is 1. There is no maximum.

It should be noted that the documentation states that Visualforce Remote Objects is still in Developer Preview and is not feature complete yet, so this may be added in a future release:

Visualforce Remote Objects offers new capabilities, but isn’t feature complete yet. Although it isn’t subject to some resource limits, it comes with limitations of its own. As a Developer Preview feature, Remote Objects is subject to change without providing backward compatibility.

---

For the time being I would suggest using Visualforce Remoting if you need to use the results of an aggregate query within your JavaScript.