Queries

To retrieve objects by more complex criteria than their id, queries can be used. They are executed on Baqend and return the matching objects. The Baqend SDK features a query builder that creates MongoDB queries under the hood. It is possible to formulate native MongoDB queries, but using the query builder is the recommend way: it is far more readable and does all the plumbing and abstraction from MongoDB obscurities.

resultList, singleResult and count

The simplest query is one that has no filter criterion and thus returns all objects. The actual result is retrieved via the resultList method.

DB.Todo.find().resultList((result) => {
  result.forEach((todo) => {
    console.log(todo.name); //'My first Todo', 'My second Todo', ...
  });
});

You can also use the depth-parameter to query the entities to a specified depth just like for normal reads.

To find just the first matching object, use the singleResult method. If there is no result, it will give you null.

DB.Todo.find().singleResult((todo) => {
  if (!todo) {
    // No todo available…    
  } else {
    console.log(todo.name); //'My first Todo'    
  }
});

Both resultList and singleResult support deep loading to also load references.

If you just need the number of matching objects, use the count method.

DB.Todo.find().count((count) => {
  console.log(count); //'17'
});

Filters

Usually queries are employed to exert some kind of filter. The query builder supports lots of different filters, that can be applied on entity attributes. By default chained filters are and-combined.

DB.Todo.find()
  .matches('name', /^My Todo/)
  .equal('active', true)
  .lessThanOrEqualTo('activities.start', new Date())
  .resultList(...)

The above query searches for all todos, whose name starts with 'My Todo', are currently active and contain an activity in its activities list that has been started before the current date.

Note that all valid MongoDB attribute expressions can be used as a field name in a filter, in particular path-expressions such as 'activities.start'.

If you are familiar with MongoDB queries, you can use the where method to describe a query in MongoDB's JSON format. An equivalent query to the above one would look like this:

DB.Todo.find()
  .where({
    "name": { "$regex": "^My Todo" },
    "active": true,
    "activities.start": { "$lte": { "$date": new Date().toISOString() }}
  })
  .resultList(...)

The following table list all available query filters and the types on which they can be applied:

Filter method
MongoDB equivalent Supported types Notes
equal('name', 'My Todo')
$eq All types Complex types like embedded objects only match if their complete structure matches.
notEqual('name', 'My Todo')
$neq All types Complex types like embedded objects only match if their complete structure matches.
greaterThan('total', 3)
$gt Numbers, Dates, Strings gt() is an alias
greaterThanOrEqualTo('total', 3)
$gte Numbers, Dates, Strings ge() is an alias
lessThan('total', 3)
$lt Numbers, Dates, Strings lt() is an alias
lessThanOrEqualTo('total', 3)
$lte Numbers, Dates, Strings le() is an alias
between('total', 3, 5)
- Numbers, Dates, Strings It is equivalent to gt('total', 3).lt('total', 5)
in('total', 3, 5[,...])
$in All types For primitive fields any of the given values have to match the field value. On set and list fields at least one value must be contained in the collection in order for the filter to match.
notIn('total', 3, 5[,...])
$nin All types On primitive fields none of the given values must match the field value. On set and list fields none of the given values must to be contained in the collection in order for the filter to match.
isNull('name')
- All types Checks if the field has no value; equivalent to equal('name', null)
isNotNull('name')
$exists All types Checks if the field has a value; equivalent to where({ "name": { "$exists": true, "$ne": null } })
containsAny('activities', activity1, activity2 [,...])
$in List, Set, JsonArray Checks if the collection contains any of the given elements
containsAll('activities', activity1, activity2 [,...])
$all List, Set, JsonArray Checks if the collection contains all of the given elements
mod('total', 5, 3)
$mod Number The field value divided by divisor must be equal to the remainder
matches('name', /^My [eman]{4}/)
$regex String The regular expression must be anchored (starting with ^); ignore case and global flags are not supported.
size('activities', 3)
$size List, Set, JsonArray Matches if the collection has the specified size.
near('location', <geo point>, 1000)
$nearSphere GeoPoint The geo point field has to be within the maximum distance in meters to the given GeoPoint. Returns from nearest to furthest.
You need a Geospatial Index on this field, to use this kind of query. Read the query index section for more details.
withinPolygon('location', <geo point list>)
$geoWithin GeoPoint The geo point of the object has to be contained within the given polygon. You need a Geospatial Index on this field, to use this kind of query. Read the query indexes section for more details.

You can get the current GeoPoint of the User with DB.GeoPoint.current(). This only works with an HTTPS connection.

References can and should be used in filters. Internally references are converted to ids and used for filtering. To get all Todos owned by the currently logged-in user, we can simply use the User instance in the query builder:

DB.Todo.find()
  .equal('owner', DB.User.me) //any other User reference is also valid here
  .resultList(...)
Note: DB.user.me refers to the currently logged-in User instance. To learn more about users and the login process see the User, Roles and Permission chapter.

Sorting

It is possible to sort the query result for one or more attributes. The query builder can be used to specify which attributes shall be used for sorting. Let's sort our query result by name:

DB.Todo.find()
  .matches('name', /^My Todo/)
  .ascending('name')
  .resultList(...)

If you use more than one sort criterion, the order of the result reflects the order in which the sort methods were called. The following query will list all active tasks before the inactive ones and sort the tasks by their name in ascending order.

DB.Todo.find()
  .matches('name', /^My Todo/)
  .ascending('name')
  .descending('active')
  .resultList(...)

When calling descending('active') before ascending('name') the result is sorted by name and then by active flag, which is only relevant for multiple todos having the same name.

You can also set the sort criteria with the MongoDB orderby syntax by using the sort() method. An equivalent expression to the above is this:

DB.Todo.find()
  .matches('name', /^My Todo/)
  .sort({"name": 1, "active": -1})
  .resultList(...)

Offset and Limit

On larger data sets you usually don't want to load everything at once. Its often reasonable to instead page through the query results. It is therefore possible to skip objects and limit the result size.

var page = 3;
var resultsPerPage = 30;

DB.Todo.find()
  .matches('name', /^My Todo/)
  .ascending('name')
  .offset((page - 1) * resultsPerPage)
  .limit(resultsPerPage)
  .resultList(...)
Note: An offset query on large result sets yields [poor query performance](http://use-the-index-luke .com/sql/partial-results/fetch-next-page). Instead, consider using a filter and sort criteria to navigate through results.

For instance if you implement a simple pagination, you can sort by id and can get the data of the next page by a simple greaterThen filter. As the id always has an index this results in good performance regardless of the query result size.

var pageId = '00000-...';
var resultsPerPage = 30;

DB.Todo.find()
  .matches('name', /^My Todo/)
  .greaterThan('id', pageId)
  .ascending('id', pageId)
  .limit(resultsPerPage)
  .resultList(function(result) {
    pageId = result[result.length - 1];  
  })

But often you want to sort your result by another attribute (e.g. createdAt). When you sort by an attribute which by itself is not unique you must combine it with a unique attribute (e.g. id).

//initialize default values
var resultsPerPage = 30;
var lastObject = {id: '', createdAt: new Date(0)};

//later page through the pages by the following query
var qb = DB.Todo.find();
qb.or(qb.equal('createdAt', lastObject.createdAt).greaterThan('id', lastObject.id), qb.greaterThan('createdAt', lastObject.createdAt))
     .ascending('createdAt')
     .ascending('id')
     .limit(resultsPerPage)
     .resultList(function(result) {
        //track last seen object
        lastObject = result[result.length - 1];
        console.log(result);
      });

Explanation: By combining the results of the query which fetches all remaining entities where the createdAt is equal to our last seen createdAt plus all ids which are greater than the last seen id we make our result unique when createdAt has the same value on multiple entities. That guarantees a unique order for none unique attributes.

Composing Filters by and, or and nor

Filters are joined with and by default. In more complex cases you may want to formulate a query with one or more and, or or nor expressions. For such cases the initial find() call returns a Query.Builder instance. The builder provides additional methods to compose filter expressions.

The following query finds all todos which the logged-in user is not currently working on and all todos which aren't done yet:

var queryBuilder = DB.Todo.find();
var condition1 = queryBuilder
  .matches('name', /^My Todo/)
  .equal('active', false);

var condition2 = queryBuilder
  .matches('name', /^Your Todo/)
  .equal('done', false);

queryBuilder.or(condition1, condition2)
  .ascending('name')
  .resultList(...)

Query Indexes

Indexes on fields that are frequently queried can massively impact the overall query performance. Therefore our Dashboard provides a very comfortable way to create custom indexes on fields. It is always an tradeof on which fields you should create an index. A good index should be created on fields that contains many distinct values. But to many indexes on the same class can also reduce the write throughput. If you like to read more about indexes we currently use, visit the mongo indexes docs.

To create an Index open the schema view of the class and use the Index or Unique Index button to create an index. Currently we support three types of indexes:

Index: A simple index which contains a single field used to improve querys which filters the specified field.

Unique Index: A index that requires uniqueness of the field values. Inserting or updating objects that violates the unique constraint will be rejected with an ObjectExists error.

Geospatial Index: This index can be created on GeoPoint fields and is required for near and withinPolygon query filters. This Index is created on GeoPoint fields by using the Index Button.

Proceed to next Chapter: Real-Time Queries