LogoLogo
What's NewDeveloper CommunitySupportStatus
  • 🚀Get Started
    • What is JourneyApps Platform?
    • Tutorial: Build your First App
      • 1. Introduction
      • 2. Create a new App
      • 3. OXIDE IDE Overview
      • 4. Hello World app
      • 5. The Data Model
      • 6. View Components
      • 7. Queries and Data Sync
      • 8. Simple Navigation
      • 9. View Stack
      • 10. Input Validation
      • 11. View Parameters
      • 12. Data Manipulation
      • 13. Responsive Apps
      • 14. Styling
      • 15. Lists
      • 16. GPS Capturing
      • 17. Relationships
      • 18. Multiple User Roles
      • 19. Deployment and Users
      • 20. Version Control
      • 21. CSV and APIs
      • 22. Conclusion
    • JourneyApps Platform Fundamentals
      • Creating a New App
        • Git-enabled Apps
      • What are Views?
      • What is the Data Model?
      • JourneyApps Syntax Basics
      • Access the Database (DB)
        • Manipulate DB Objects
        • Query DB Objects
      • View Navigation
        • Deep Linking
      • CloudCode Overview
      • OXIDE (Online IDE)
  • 💻Build your App
    • JourneyApps Syntax
      • Syntax Basics
      • Access the DB
      • View Navigation
      • Async & Await
      • TypeScript Apps (Beta)
        • runtime-build package
        • TypeScript App Troubleshooting
      • What's New in V4
        • Updating to the V4 API
    • Configure your Data Model
      • What is the data model?
      • Reference: model
        • field
        • belongs-to
        • has-many
        • index
      • Data Rules
        • Data Buckets
        • Sync Rules - Limit data synced to devices
        • Data ACLs - Limit access to data
        • Real-world example for Data Rules
        • ❔FAQs
        • Migrate to Data Rules
      • App Indexes
      • Webhooks
    • UI Components
      • All UI Components
        • actionSheet
        • Attachments
        • button
        • button-group
        • capture-coordinates
          • marker
          • marker-query
        • capture-file
        • capture-photo
        • capture-signature
        • card
          • accent
          • action
        • columns
          • column
        • component
        • context-menu
          • divider
          • item
        • CSV
        • date-input
        • datetime-input
        • dialog
          • body
        • display-3d-model
          • 📖display-3d-model Guides
            • Guide 1: Initialize and layout a 3D model in a view
            • Guide 2: Control playback position
            • Guide 3: Troubleshooting controls
        • display-coordinates
        • display-file
        • display-image
        • display-photo
        • display-signature
        • heading
        • html
          • HTML Advanced Topics
          • ❔HTML FAQs
          • 📖Guide: HTML & JourneyApps iFrame Client
        • icons
        • info
        • info-table
          • row
        • journey.photos (capture multiple photos)
        • JourneyPrinter (print PDFs)
        • grid
          • cell
          • 📖grid Examples
        • list
          • list-item
            • accent
            • asset
            • pills
              • pill
            • action
        • multiple-choice-checklist
        • navigation (Navigation drawer)
          • general-section
            • item
          • section
            • item
              • item
          • ❔navigation FAQs
        • notification
        • object-dropdown
        • object-list
          • action
        • object-repeat
        • object-table
          • action
          • column
            • action
            • edit-boolean
            • edit-date
            • edit-datetime
            • edit-integer
            • edit-number
            • edit-select
            • edit-text
            • edit-time
            • edit-typeahead
              • action
            • header-action
          • column-group
          • empty-action
          • 📖object-table Guides
            • Actions
            • Cell callouts
            • Column groups
            • Columns
            • Controlled object-table
            • Controls
            • Copy & paste data
            • Edit cells
            • Filters
            • Frozen columns
            • Fullscreen object-table
            • Mode
            • State
            • Styles
        • optionList
        • PhotonSync (transfer data offline)
        • power-bi
          • 📖Guide: PowerBI Embedding
        • scan-barcode
        • shortcut
        • sidebar
        • single-choice-dropdown
        • single-choice-radio
        • template
        • text-input
        • time-input
        • toggle
        • view
      • JS/TS Events
      • Show / Hide UI Components
      • View Templates
      • XML Fields (Attributes)
        • align-content
        • align-controls
        • align-label
        • bind
        • clear-button-visibility
        • control-order
        • disabled
        • error-message
        • icon-position
        • id
        • hide-if
        • modifier-text
        • label
        • label-case
        • label-color
        • on-change
        • on-press
        • placeholder
        • required
        • show-if
    • JS / TS APIs
      • Attachment
      • Bluetooth (Beta)
      • Broadcast
      • component
      • CSV
      • DB
      • HardwareBarcode
      • journey
        • journey.config
        • journey.container
        • journey.device
        • journey.diagnostics
        • journey.dialog
        • journey.files
        • journey.hardware
        • journey.photos
        • journey.runtime
        • journey.sensors
        • journey.viewStack
      • JourneyPrinter
      • KeyboardBarcode
      • LocalDB
      • NFC
      • OnlineDB
      • PhotonSync
      • SerialPort
      • ShortcutManager
      • TCPSocket
      • user
    • Extend your App with Custom Code
      • App packages
        • App packages overview
        • PDF report package
        • TypeScript library & unit tests
        • Manage External Dependencies
      • Custom HTML
    • Style & Customize your App
      • Style & configure UI components
        • Overview
        • Understand extendable themes
        • Use themes on a view
        • Theme specific components on a view
        • Examples
        • Debugging
        • ❔FAQs
      • Change your App Font
      • Custom Branding
        • Custom Container Features
        • Special Requirements for iOS Containers
    • Integrate your App
      • Backend integrations with CloudCode
      • Barcode Scanning
        • Barcode Scanning using Keyboard Emulation
        • Hardware Barcode Scanning
        • scan-barcode
      • Bluetooth Low Energy (BLE)
      • Broadcast API
      • HTTP requests (Fetch API)
      • JourneyApps Print (Android)
      • Maps and navigation
      • NFC
      • Opening external links/apps
      • Serial Port
      • TCP Sockets
    • Design Intuitive Apps
      • UX Guidelines
      • Write Effective Copy
  • 📱App Features
    • RealWear® Voice Control
      • Automatic Voice Commands
        • Automatic Voice Commands - Advanced
      • Manual Voice Commands
    • App, Runtime and Container Updates
    • Batch Operations (App)
    • Call JS/TS Functions from XML
    • Capture GPS Locations
    • Push Notifications
    • Translations
    • XML Format Strings
    • Webhooks (External)
  • 🌐CloudCode
    • CloudCode Overview
    • Trigger a CloudCode Task
      • Trigger CC with a Schedule
      • Trigger CC via a Webhook
      • Trigger CC from an App
      • Trigger CC from Another Task
      • Trigger CC via HTTP
    • Attachments in CloudCode
    • Timezones
    • Advanced CloudCode Topics
      • Access Multiple DBs in CloudCode Tasks
      • Batch API (CloudCode)
      • CloudCode Dependencies
      • Configure HTTPS in CloudCode
      • Deployment environment variables
      • Local CloudCode Development
      • PDF Reports using CloudCode
      • Shared CloudCode Tasks
      • Translations in CloudCode
  • 📥Backend API
    • Introduction
    • API Reference
      • Retrieve All Objects
      • Query Objects
      • Sort Results
      • Limit and Skip
      • Count Objects
      • Create a New Object
      • Retrieve a Single Object
      • Update a Single Object
      • Delete a Single Object
      • Batch Operations (v4 API)
      • Oplog API
      • Retrieve the App Data Model
      • Manage App Users and Sessions
      • Field Representation
      • Error Responses
    • API Limits
    • Update to the V4 API
  • ⚙️Technical
    • Data Synchronization Priority
    • Device Diagnostics
    • JSON1 Query Engine
    • Improve App Performance
    • Security Measures
    • Supported Platforms
      • Web Container
      • Windows Installer
    • Domain Whitelist
  • 🖥️OXIDE
    • Get started with OXIDE
      • OXIDE Overview
      • Components of OXIDE
    • Configure Testing Deployments
    • Edit and Manage Files
      • How to Navigate to a Function
      • Manage External Dependencies
    • Create and Manage App Containers
    • Debugging & Troubleshooting
      • Common Troubleshooting Pointers
      • App Diagnostics Reports
      • Build Logs
    • OXIDE Workspaces
      • OXIDE Trees
  • ❕Deprecated Features
    • Deprecated Features and Components
Powered by GitBook
On this page
  • Query Reference Table Summary
  • Listing All Objects
  • Listing All Objects in a Relationship
  • Counting Results
  • Filtering
  • Sorting
  • Limiting Results
  • Skipping Records
  • Deleting
  • Looping Through Objects
  • Include related objects in a query
  • Arrays
  • Guidelines for choosing between array and query
  1. Get Started
  2. JourneyApps Platform Fundamentals
  3. Access the Database (DB)

Query DB Objects

PreviousManipulate DB ObjectsNextView Navigation

Last updated 2 years ago

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

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

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.

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.

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

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

The user interacts with (modifies) the data, for example a in an .

Please see these when querying large datasets.

🚀
working with massive queries
text-input
object-repeat
app performance considerations
Async and Await
this section
List all objects
List all children in a has-many relationship
List a parent in a belongs-to relationship
Count the number of objects
Filter using query conditions
Find the first object for a set of conditions
Order / sort results in a query
Limit the number of results in a query
Skip a number of results
Delete all results in a query
Include related objects in the query response
OnlineDB