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.
Here is a table that summarizes the various queries available to developers in JourneyApps:
I want to: | Query Syntax | Example |
|---|---|---|
.all() | DB.person.all() | |
parent.children | household.members | |
child.parent() | member.household() | |
.count(n) | DB.person.all().count() | |
.where(query_string, arguments) | DB.person.where("name = ?", "John") | |
.first(query_string, arguments) | DB.person.first("name = ?", "John") | |
.orderBy(field) | DB.asset.all().orderBy("make") | |
.limit(n) | DB.asset.all().limit(5) | |
.skip(n) | DB.asset.all().skip(5) | |
.destroyAll() | DB.asset.all().destroyAll() | |
.include(model) | DB.person.all().include("city.province.country") |
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()orforEach()(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.
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.
To determine the number of results returned by a query, use its
count() function:main.js
var number_of_persons = DB.person.all().count();
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"]);
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");
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);
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);
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();
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();
}
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();
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.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.
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.
Last modified 1yr ago