# 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"`.

{% code title="main.xml" %}

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

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

{% endcode %}

{% hint style="info" %}
**Query Syntax for CloudCode and TypeScript Apps**

Queries are asynchronous operations, so please refer to the [Async and Await](https://docs.journeyapps.com/reference/build/syntax/async-and-await) syntax docs for TypeScript apps or for queries in CloudCode Tasks.
{% endhint %}

{% hint style="info" %}
**Working with Arrays**

For some use cases, you need to work with data in Arrays. Read [this section](#arrays) to learn more about arrays in JourneyApps.
{% endhint %}

### Query Reference Table Summary

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

<table><thead><tr><th>I want to:</th><th width="237.4717243951842">Query Syntax</th><th>Example</th></tr></thead><tbody><tr><td><a href="#listing-all-objects">List all objects</a></td><td><code>.all()</code></td><td><code>DB.person.all()</code></td></tr><tr><td><a href="#listing-all-objects-in-a-relationship">List all children in a has-many relationship</a></td><td><code>parent.children</code></td><td><code>household.members</code></td></tr><tr><td><a href="#listing-all-objects-in-a-relationship">List a parent in a belongs-to relationship</a></td><td><code>child.parent()</code></td><td><code>member.household()</code></td></tr><tr><td><a href="#counting-results">Count the number of objects</a></td><td><code>.count(n)</code></td><td><code>DB.person.all().count()</code></td></tr><tr><td><a href="#filtering">Filter using query conditions</a></td><td><code>.where(query_string, arguments)</code></td><td><code>DB.person.where("name = ?", "John")</code></td></tr><tr><td><a href="#filtering">Find the first object for a set of conditions</a></td><td><code>.first(query_string, arguments)</code></td><td><code>DB.person.first("name = ?", "John")</code></td></tr><tr><td><a href="#sorting">Order / sort results in a query</a></td><td><code>.orderBy(field)</code></td><td><code>DB.asset.all().orderBy("make")</code></td></tr><tr><td><a href="#limiting-results">Limit the number of results in a query</a></td><td><code>.limit(n)</code></td><td><code>DB.asset.all().limit(5)</code></td></tr><tr><td><a href="#skipping-records">Skip a number of results</a></td><td><code>.skip(n)</code></td><td><code>DB.asset.all().skip(5)</code></td></tr><tr><td><a href="#skipping-records">Delete all results in a query</a></td><td><code>.destroyAll()</code></td><td><code>DB.asset.all().destroyAll()</code></td></tr><tr><td><a href="#include-related-objects-in-a-query">Include related objects in the query response</a></td><td><code>.include(model)</code></td><td><code>DB.person.all().include("city.province.country")</code></td></tr></tbody></table>

### Listing All Objects

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

{% code title="main.js" %}

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

{% endcode %}

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

{% code title="main.js" %}

```javascript
view.all_members = view.household.members;
```

{% endcode %}

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

{% code title="main.js" %}

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

{% endcode %}

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:

{% code title="main.js" %}

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

{% endcode %}

### Filtering

Queries may be filtered, using the `where` function:

{% code title="main.js" %}

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

{% endcode %}

To find a single object, use `first`:

{% code title="main.js" %}

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

{% endcode %}

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:

{% code title="main.js" %}

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

{% endcode %}

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

{% code title="main.js" %}

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

{% endcode %}

String matching is possible - always case insensitive:

{% code title="main.js" %}

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

{% endcode %}

Relationships may also be filtered further:

{% code title="main.js" %}

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

{% endcode %}

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

{% code title="main.js" %}

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

{% endcode %}

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

{% code title="main.js" %}

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

{% endcode %}

### Sorting

Queries may be sorted by one or more fields:

{% code title="main.js" %}

```javascript
// 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");
```

{% endcode %}

### Limiting Results

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

{% code title="main.js" %}

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

{% endcode %}

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

{% code title="main.js" %}

```javascript
// 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);
```

{% endcode %}

### Deleting

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

{% code title="main.js" %}

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

{% endcode %}

### Looping Through Objects

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

{% code title="main.js" %}

```javascript
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;
}
```

{% endcode %}

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

{% code title="main.js" %}

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

{% endcode %}

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

{% code title="main.js" %}

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

{% endcode %}

### Include related objects in a query

{% hint style="info" %}
**Container compatibility**

`.include()` was introduced in version **4.29.0** of the JourneyApps Container.
{% endhint %}

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

{% code title="main.js" %}

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

{% endcode %}

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:

{% code title="main.js" %}

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

{% endcode %}

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:

{% code title="main.js" %}

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

{% endcode %}

### 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:

{% code title="main.xml" %}

```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>
```

{% endcode %}

{% code title="main.js" %}

```javascript
// 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);
```

{% endcode %}

{% hint style="info" %}
**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.
{% endhint %}

### 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](https://docs.journeyapps.com/reference/technical/improving-app-performance) (hundreds to thousands of objects), and the query should only be executed once for performance reasons.
* You are using [OnlineDB](https://docs.journeyapps.com/reference/get-started/journeyapps-fundamentals/accessing-the-database/..#onlinedb) (to avoid slow reloading of the queries).
* The user interacts with (modifies) the data, for example a [`text-input`](https://docs.journeyapps.com/reference/build/ui-components/all-ui-components/text-input) in an [`object-repeat`](https://docs.journeyapps.com/reference/build/ui-components/all-ui-components/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.

{% hint style="warning" %}
Please see these [app performance considerations](https://docs.journeyapps.com/reference/technical/improving-app-performance) when querying large datasets.
{% endhint %}