Querying Entities

Retrieving data efficiently is a fundamental aspect of any database system. With Onyx Database, you can execute complex queries to fetch the data you need. This tutorial demonstrates how to query for data in Onyx Database. As an example, we will retrieve a list of "Player" entities, specifically searching for quarterbacks with a last name that starts with "C" and ordering them by first name in ascending order.

Steps to Query Entities (Onyx Cloud Database)

1

Declare the Schema

Use the provided JSON schema to define your entity in the Onyx Cloud Database.
1{
2  "tables": [
3    {
4      "name": "Player",
5      "identifier": {
6        "name": "id",
7        "type": "Long",
8        "generator": "Sequence"
9      },
10      "partition": "",
11      "attributes": [
12        {
13          "name": "id",
14          "type": "Long"
15        },
16        {
17          "name": "firstName",
18          "type": "String"
19        },
20        {
21          "name": "lastName",
22          "type": "String"
23        },
24        {
25          "name": "position",
26          "type": "String"
27        }
28      ],
29      "relationships": [],
30      "indexes": []
31    }
32  ],
33  "revisionDescription": "Schema for querying entities tutorial"
34}
  • Ensure your schema defines all required attributes, including types and identifiers.
  • The identifier field is mandatory for each table and must include a unique name and a supported generator type.
2

Build the Query Using QueryBuilder

Utilize the QueryBuilder to construct your query. In this example, we retrieve players who have a position equal to "QB" and a lastName starting with "C", ordering them by firstName.
1// Build the query using the QueryBuilder syntax
2const query = db.from("Player")
3    .where(eq("position", "QB").and(startsWith("lastName", "C")))
4    .orderBy(asc("firstName"));
  • Use the from function to specify the entity type.
  • Combine criteria using infix functions and logical operators.
3

Execute the Query

Use the <code>list()</code> function to execute the query and retrieve the results.
1// Execute the query and get the results
2const quarterbacks = await query.list();
4

Print the Results

Iterate over the result set and print out the names of the quarterbacks.
1for (const qb of quarterbacks) {
2    console.log(`${qb.firstName} ${qb.lastName}`);
3}
Next Tutorial: Compound Query

Important Notes

  • Ensure that your entity classes are properly annotated with @Entity and that all necessary fields are annotated with @Attribute.
  • When using the QueryBuilder, leverage Kotlin's infix functions to build readable and maintainable queries.
  • The orderBy function can accept multiple fields to sort the results in the specified order.

Troubleshooting

  • No Results Returned: Verify that your query criteria correctly match the data in your database.
  • Incorrect Sorting: Ensure that the field specified in orderBy exists and is correctly spelled.
  • Exceptions Thrown: Check for typos in attribute names and ensure that the operators used are compatible with the attribute types.

Next Steps

Now that you know how to execute basic queries using the QueryBuilder, you can explore more advanced querying techniques: