Query DB Objects

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

main.xml

<view ...>
    <param name="household" type="household" />
    <var name="all_members" type="query:person" />

    <object-list query="all_members" label="Members" />
</view>

Query Syntax for CloudCode and TypeScript Apps

Queries are asynchronous operations, so please refer to the Async and Await syntax docs for TypeScript apps or for queries in CloudCode Tasks.

Working with Arrays

For some use cases, you need to work with data in Arrays. Read this section to learn more about arrays in JourneyApps.

Query Reference Table Summary

Here is a table that summarizes the various queries available to developers in JourneyApps:

I want to:	Query Syntax	Example
List all objects	.all()	DB.person.all()
List all children in a has-many relationship	parent.children	household.members
List a parent in a belongs-to relationship	child.parent()	member.household()
Count the number of objects	.count(n)	DB.person.all().count()
Filter using query conditions	.where(query_string, arguments)	DB.person.where("name = ?", "John")
Find the first object for a set of conditions	.first(query_string, arguments)	DB.person.first("name = ?", "John")
Order / sort results in a query	.orderBy(field)	DB.asset.all().orderBy("make")
Limit the number of results in a query	.limit(n)	DB.asset.all().limit(5)
Skip a number of results	.skip(n)	DB.asset.all().skip(5)
Delete all results in a query	.destroyAll()	DB.asset.all().destroyAll()
Include related objects in the query response	.include(model)	DB.person.all().include("city.province.country")

Listing All Objects

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

main.js

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

Note that a query is not executed until:

1. It is being displayed in a view component.

2. A method is called that operates on the results, e.g. toArray(), first(), count() or forEach() (see below).

In view components, a query will automatically be executed again when the results may have changed, for example after a button press or a successful sync.

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"):

main.js

view.all_members = view.household.members;

To query a household from a member, do a lookup through the member (remember that we defined the inverse name of the relationship as "household"):

main.js

view.household = view.member.household();

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:

main.js

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

Filtering

Queries may be filtered, using the where function:

main.js

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

To find a single object, use first:

main.js

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

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

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

main.js

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.

main.js

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

String matching is possible - always case insensitive:

main.js

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

Relationships may also be filtered further:

main.js

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

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

main.js

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

Queries can match against multiple values using the in or not in operators:

main.js

DB.asset.where("archived in ?", [false, null]);
DB.asset.where("make not in ?", ["Nokia", "Samsung"]);

Sorting

Queries may be sorted by one or more fields:

main.js

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

Limiting Results

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

main.js

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

Skipping Records

You can skip a certain number of records by using the skip() function as shown below. This is useful in combination with limit() for "paging" use cases where you only want to show X results at a time, and users should be able to scroll through all results (e.g. in a table):

main.js

// This will return all objects after the first 5 results.
DB.asset.all().orderBy("date_added").skip(5);

// This will return up to 5 objects after the first 5 results. I.e. the 6th to 10th results.
DB.asset.all().limit(5).skip(5);

Deleting

All objects in a query can be deleted from the database with destroyAll():

main.js

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

Looping Through Objects

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

main.js

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

You can also use the forEach() function on a query to loop through the objects:

main.js

var total_salary = 0;
DB.person.all().forEach(function(person) {
    total_salary += person.salary;
});

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

main.js

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

Include related objects in a query

Container compatibility

.include() was introduced in version 4.29.0 of the JourneyApps Container.

Iterating through objects and lookup up a belongs-to relationship can be done as follows:

main.js

var people = DB.person.all().toArray();
people.forEach(function(person) {
    console.log('city', person.city());
});

However, this would perform an individual query for each city, which would be slow. To avoid this performance issue, include the relationship in the query:

main.js

var people = DB.person.all().include('city').toArray();
people.forEach(function(person) {
    console.log('city', person.city());
    });

This loads the city belongs-to relationship as part of the query, which is significantly faster.

It is also possible to include multiple relationships, as well as nested relationships:

main.js

DB.person.all().include('city.province.country', 'company').toArray();

Arrays

In version 4.6 and later of the JourneyApps Container, an array view variable type is available in addition to the query type. It operates similarly to query variables, but stores the results instead of the source query.

This has several implications:

1. The array may be constructed in other ways than a normal query.

2. The objects in the array don't need to have been saved.

3. The array is not automatically reloaded like queries.

Examples:

main.xml

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

    <var name="items" type="array:item" />

    <object-list query="all_members" label="Members" />

    <object-table query="items">
        <column heading="Description">{description}</column>
        <column heading="Quantity">{quantity}</column>
    </object-table>
</view>

main.js

// 1. From a query
// Note: .toArray() is important! A query cannot be directly assigned to the
// view variable.
view.all_members = DB.person.all().toArray();

// 2. From a has-many relationship
view.all_members = view.household.members.toArray();

// 3. Constructed directly
view.all_members = [
    DB.person.create({name: 'First'}),
    DB.person.create({name: 'Second'})
];

// 4. Construct in a loop
view.all_members = [];
for(var i = 0; i < 10; i++) {
    view.all_members.push(DB.person.create({code: 'P' + i}));
}

// 5. Add a "footer" to a table
view.items = DB.item.all().toArray();

var total = 0;
for(var i = 0; i < items.length; i++) {
    var item = view.items[i];
    total += item.quantity;
}

var footer = DB.item.create();
footer.description = 'Total';
footer.quantity = total;
view.items.push(footer);

Important updates made in v4.27 of the JourneyApps Container

We have rewritten some internal code to fix a number of bugs and inconsistencies in the way query and array variables are handled together with object-table, object-dropdown, object-list and object-repeat components. Along with this, we've also applied performance improvements.

Guidelines for choosing between array and query

Use array if any of these are true:

• You need to display unsaved objects.

• You need to display a list that cannot be represented as a direct database query.

• You are working with massive queries (hundreds to thousands of objects), and the query should only be executed once for performance reasons.

• You are using OnlineDB (to avoid slow reloading of the queries).

• The user interacts with (modifies) the data, for example a text-input in an object-repeat.

Use query if:

• You want automatic reloading when data changes in the database, for example when the user syncs new data.

• Your case is not covered by the list for arrays.

Please see these app performance considerations when querying large datasets.