Brightspot CMS Developer Guide

Advanced Querying

The Query API includes options to retrieve objects as reference-only, so called because only the id and typeId fields are set to actual values. The other fields in a reference-only object are set to null for object types, zero for primitive number types, and false for a primitive boolean type.

A reference-only object is represented as follows in a JSON object:

dari.tutorial.Activity: 0000015e-9faa-d59e-a17e-9fae6bd80000
   "_id" : "0000015e-9faa-d59e-a17e-9fae6bd80000",
   "_type" : "0000015e-9fa9-d59e-a17e-9fadebfc0000"

The reference-only object itself cannot be modified and saved. But you can use the ID of the reference-only object to retrieve another object with all of the data.

Activity activityFull = Query.from(Activity.class).where("_id = ?", activityRef.getId()).first());

Query reference options are intended to reduce network traffic and optimize database calls. The following options are useful when only the Ids of retrieved objects are needed, at least initially.

The referenceOnly option sets the query to return objects with actual values set only on the id and typeId fields.

Using the State#isReferenceOnly() method, you can test if a retrieved object is reference only.

Activity activityRef = Query.from(Activity.class).referenceOnly().first();
return activityRef.getState().isReferenceOnly(); // returns true

One use case for referenceOnly is to limit the amount of data that can be retrieved at one time. For example, the following snippet retrieves SiteMap objects, which represent all of the pages on a website. Because a SiteMap can be extremely large, the referenceOnly option is used in the query to retrieve the objects. The goal is to minimize the amount of data returned with any one database call.

List<SiteMapSummary> summaries = new ArrayList<>(); 

for (SiteMap siteMapReference : Query.from(SiteMap.class).referenceOnly().iterable(0)) { 
    UUID id = siteMapReference.getId(); 
    if (id != null) {
        /* Resolve only one at a time, since site maps can be very large. */
        SiteMap siteMap = Query.from(SiteMap.class).where("_id = ?", id).first(); 
        if (siteMap != null) {
            summaries.add(new SiteMapSummary(siteMap)); 

  • Initializes a list of SiteMapSummary objects to which all of the SiteMap objects will be added.
  • Creates an iterable set of SiteMap objects with a query that retrieves reference-only SiteMap objects.
  • Retrieves each SiteMap object individually, spreading data retrieval across several database calls.
  • Retrieves the corresponding SiteMap object.
  • Adds the retrieved SiteMap object to the list.

The resolveToReferenceOnly option impacts objects that reference other objects, such as an Activity object with fields for User and Project objects. The objects that are referenced (User and Project) are returned as reference-only objects. All other fields of the referencing object (Activity) are set to their actual values.

Using the State#isResolveToReferenceOnly() method, you can test if a retrieved object contains reference-only objects.

Activity activityResolve = Query.from(Activity.class).resolveToReferenceOnly().first();
return activityResolve.getState().isResolveToReferenceOnly(); // returns true

Records being reindexed are queried for using resolveToReferenceOnly, so any indexed methods which depend on values in referenced (non-embedded) records will not be calculated correctly. You can work around this issue by explicitly resolving the references in the indexed method.

The following example shows an indexed method from an Article class that explicitly resolves references to Author objects. Reindexing of Article objects invokes the getAuthorNames method, which returns a list of one or more author names.

public List<String> getAuthorNames() {
    List<Author> authors; 
    if (getState().isResolveToReferenceOnly()) { 
        Article article = (Article) Query.fromAll().where("id = ?", this).first();
        if (article != null) {
            authors = article.getAuthors();
        } else {
            authors = Collections.emptyList();
    } else {
        authors = getAuthors(); 
    return authors 

public List<Author> getAuthors() {
    if (authors == null) {
         authors = new ArrayList<>();
    return authors;
  • Initializes a list of Author objects.
  • Tests if the Article object contains reference-only objects. If it does, the ID from the object is used to retrieve another Article object that contains all of the Author objects.
  • If there are embedded authors, retrieves them.
  • Collects the found authors into a list.

Dari supports spatial queries on the MySQL, PostgreSQL, and Solr databases. To use Dari’s spatial features, define a field of type com.psddev.dari.db.Location on a model that will be queried with spatial lookups. The Location type is a container for latitude and longitude values. Annotate the field with @Indexed.

For example:

public class Venue {
    private String name;
    private Location location;


The following query finds all venues within a 10-mile radius of Reston Town Center in Reston, VA:

double degrees = Region.milesToDegrees(10);
double y = -77.24234;
double x = 34.55454;

PaginatedResult<Venue> venues = Query.from(Venue.class)
          .where("location = ?", Region.sphericalCircle(y, x, degrees))
          .select(0, 10);

You can also sort venues with the sortClosest or sortFarthest method. The following query is modified with the sortCloset method, which sorts results in closet order from the given location.

double degrees = Region.milesToDegrees(10);
double y = -77.24234;
double x = 34.55454;

PaginatedResult<Venue> venues = Query.from(Venue.class)
           .where("location = ?", Region.sphericalCircle(y, x, degrees))
           .sortClosest("location", new Location(y, x))
           .select(0, 10);
For optimal performance with sortClosest or sortFarthest, use the where method to limit the results to be inside a given distance.

QueryFilter is an interface for in-memory filtering of paginated results. Implementations are invoked by the Query#selectFiltered(long offset, int limit, QueryFilter filter) method. When called, the implementation evaluates each object within the range of a given offset and limit, and returns a boolean value indicating whether the object is included or excluded from the paginated results.

selectFiltered calls are high-risk operations, as they can result in excessive database queries and memory usage. Whenever possible, use indexed methods to simplify search filtering requirements.

The following snippet uses the selectFiltered method to find articles to which a specified author contributed. The method passes each of 10 articles to a query filter that is implemented as an anonymous class. The implementation retrieves all of the contributing authors of an article and iterates over them, comparing the last name of each author to the specified author name, set on the lastName variable. If there’s a match, true is returned, otherwise false.

Example QueryFilter implementation: not recommended

QueryFilter<Object> queryFilter = item -> {
    List<Author> authors = ((Article)item).getAuthors();
    for (Author author : authors) {
        if (author.getLastName().equalsIgnoreCase(lastName)) return true;
    return false;

PaginatedResult<Article> results = Query.from(Article.class)
                                        .selectFiltered(0, 10, queryFilter);

An efficient alternative to the above snippet is to add an indexed method to the Article class.

Example Indexed Method implementation: recommended

 public List<String> getAuthorLastNames() {
     List<String> lastNames = new ArrayList<>();
     List<Author> authors = List<Author> getAuthors();
     for (Author author : authors) {
     return lastNames;

By providing an indexed method on Article, the method can be queried directly for a specified last name, set on the lastName variable.

Query query = Query.from(Article.class);
PaginatedResult<Article> result = query.from(Article.class)
                                       .where("getAuthorLastNames = ?", lastName)
                                       .select(0, 10);

To view the corresponding SQL select statement (for debugging purposes) from a Dari query

Query yourQuery = Query.from(Article.class).where("headline = ?", "Foo");
return Database.Static.getFirst(MySQLDatabase.class).buildSelectStatement(yourQuery);

The above code would display an SQL statement similar to the following:

SELECT 'r'.'id', 'r'.'typeId', 'r'.'data' FROM 'Record' AS 'r'
  JOIN 'RecordString4' AS 'i0'
  ON ('i0'.'id' = 'r'.'id' 
    AND 'i0'.'typeId' = 'r'.'typeId' 
    AND 'i0'.'symbolId' IN (51))
  WHERE ('r'.'typeId' IN (X'4f8e492c6f2f390ebc61f176d3a37ab9')
    AND 'i0'.'value' IS NOT NULL
    AND 'i0'.'symbolId' = 51
    AND 'i0'.'value' IN (X'666f6f'))
  LIMIT 2147483647;

Previous Topic
Next Topic
Was this topic helpful?
Thanks for your feedback.