# 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 %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.journeyapps.com/reference/get-started/journeyapps-fundamentals/accessing-the-database/querying-db-objects.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.