Running Queries from JavaScript

Queries are stored in view variables, just like any other variable. A query is defined as an object variable, with the attribute array="true".

Example:

<view ...>
    <param name="household" type="household" />
    <var name="all_members" type="person" array="true" />

    <list collection="all_members" type="static" label="Members" />
</view>

Listing All Objects

To query all members, use DB.person.all():

view.all_members = DB.person.all();

Listing All Objects in a Relationship

To query all members in a household, do a lookup through the household (remember that we defined the name of the relationship as "members"):

view.all_members = view.household.members;

Note that the result of a query is a Query object, not a regular array. The query is automatically re-evaluated when the view is reloaded.

Counting Results

To determine the number of results returned by a query, use its count() function:

var numberOfPersons = DB.person.all().count();

Filtering

Queries may be filtered, using the where function:

view.all_members = DB.person.where("name = ?", "John");

To find a single object, use get:

view.asset = DB.asset.get("barcode = ?", "A12345");

Note that if multiple objects match the query with get, an arbitrary one is returned.

Filtering may be performed on multiple attributes by combining them with and or or, for example:

DB.asset.where("make = ? and model = ?", "Nokia", "5800")

On numbers, any of the = != > < >= <= operators may be used. Complex conditions may also be constructed with parentheses.

DB.asset.where("active != ? or (condition > ? and condition < ?)", 1, 5, 10)

String matching is possible - always case insensitive:

DB.asset.where("serial starts with ?", '123')
DB.asset.where("serial contains ?", '123')

Relationships may also be filtered further:

building.assets.where("make = ?", 'Nokia')

Queries may be chained - internally this combines the individual parts with and:

var assets = DB.asset.where("make = ?", "Nokia");
if(view.model) {
    assets = assets.where("model = ?", view.model);
}

Sorting

Queries may be sorted by one or more attributes:

// Order by model (ascending)
DB.asset.where("make = ?", "Nokia").order_by("model");
// Order by make (ascending), then by model (ascending)
DB.asset.all().order_by("make", "model");
// Order by condition (descending)
DB.asset.all().order_by("-condition");

Limiting Results

Please Note: This functionality has been available since version 3.7.5 of the JourneyApps Container on Android. If you receive an error indicating that limit() is undefined, please ensure that you've upgraded to version 3.7.5 or later if you are using Android.

You can limit the maximum number of results returned by a query using the limit() function as shown below:

// This will return a maximum of 5 objects
DB.asset.all().order_by("date_added").limit(5);

Deleting

All objects in a query can be deleted from the database with destroy_all:

// Delete all assets
DB.asset.all().destroy_all();
// Delete all assets where the make is Nokia
DB.asset.where("make = ?", "Nokia").destroy_all();

Looping Through Objects

Calling fetch() on a query returns a normal JavaScript array that you can use to loop through objects:

var totalSalaries = 0;
var people = DB.person.all().fetch();
console.log(people.length);
for (var i = 0; i < people.length; i++) {
  totalSalaries += people[i].salary;
}

If an object is modified in a loop, it must be saved manually:

for (var i = 0; i < people.length; i++) {
  people[i].salary += 1000.0;
  people[i].save();
}