Query DB Objects
Last updated
Last updated
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"
.
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 |
---|---|---|
To query all members, use DB.person.all()
:
Note that a query is not executed until:
It is being displayed in a view component.
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.
To query all members in a household, do a lookup through the household (remember that we defined the name of the relationship as "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"):
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:
Queries may be filtered, using the where
function:
To find a single object, use first
:
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:
On numbers, any of the = != > < >= <=
operators may be used. Complex conditions may also be constructed with parentheses.
String matching is possible - always case insensitive:
Relationships may also be filtered further:
Queries may be chained - internally this combines the individual parts with and
:
Queries can match against multiple values using the in
or not in
operators:
Queries may be sorted by one or more fields:
You can limit the maximum number of results returned by a query using the limit()
function as shown below:
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):
All objects in a query can be deleted from the database with destroyAll()
:
Calling toArray()
on a query returns a normal JavaScript/TypeScript array that you can use to loop through objects:
You can also use the forEach()
function on a query to loop through the objects:
If an object is modified in a loop, it must be saved manually:
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:
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:
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:
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:
The array may be constructed in other ways than a normal query.
The objects in the array don't need to have been saved.
The array is not automatically reloaded like queries.
Examples:
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.
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.