Compound Query Tutorial

To retrieve a list of entities with compound filter rules, use the executeQuery method. This tutorial demonstrates how to query data in Onyx Database and filter by transitive attributes from related entities. We will retrieve a list of Player entities, specifically searching for running backs who have achieved 1000 or more rushing yards.

Steps to Perform a Compound Query (Onyx Cloud Database)

1

Declare the Entities

Use the JSON schema to define your entities 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      "attributes": [
11        { "name": "id", "type": "Long" },
12        { "name": "firstName", "type": "String" },
13        { "name": "lastName", "type": "String" },
14        { "name": "position", "type": "String" }
15      ],
16      "relationships": [
17        {
18          "name": "stats",
19          "inverse": "player",
20          "inverseClass": "Stats",
21          "type": "OneToOne",
22          "fetchPolicy": "None",
23          "cascadePolicy": "All"
24        }
25      ]
26    },
27    {
28      "name": "Stats",
29      "identifier": {
30        "name": "id",
31        "type": "Long",
32        "generator": "Sequence"
33      },
34      "attributes": [
35        { "name": "id", "type": "Long" },
36        { "name": "rushingYards", "type": "Int" },
37        { "name": "passingYards", "type": "Int" }
38      ],
39      "relationships": [
40        {
41          "name": "player",
42          "inverse": "stats",
43          "inverseClass": "Player",
44          "type": "OneToOne",
45          "fetchPolicy": "None",
46          "cascadePolicy": "None"
47        }
48      ]
49    }
50  ],
51  "revisionDescription": "Schema for Player and Stats entities"
52}
  • Ensure your schema defines all required attributes and relationships.
  • The Player entity has a one-to-one relationship with the Stats entity.
2

Define the Compound Criteria

Use the QueryBuilder syntax to define your compound criteria.
1// Define the criteria using QueryBuilder syntax
2const compoundCriteria = eq("position", "RB").and(gte("stats.rushingYards", 1000));
  • Use infix functions like eq and gte to build criteria.
  • Chain criteria using and and or.
3

Create a Query Using the QueryBuilder

Build a query by specifying the entity type, criteria, and ordering.
1const query = db.from("Player")
2    .where(compoundCriteria)
3    .orderBy(desc("stats.rushingYards"));
  • Use the from<Entity>() method to specify the entity class.
  • Chain methods like where and orderBy to refine the query.
4

Execute the Query

Retrieve the list of entities matching the criteria.
1const runningBacks = await query.list();
5

Print the Results

Iterate over the results and display the information.
1for (const rb of runningBacks) {
2    console.log(`${rb.firstName} ${rb.lastName}`);
3}
4
5console.log(`* Only ${runningBacks.length} running backs reached 1000 yards in 2015.`);
Next Tutorial: Delete Query

Important Notes

  • You can filter by transitive attributes using dot notation (e.g., stats.rushingYards).
  • Onyx Database supports complex queries with unlimited nested attribute depth.
  • Remember to handle resources properly, such as closing the PersistenceManagerFactory in the open source version.

Troubleshooting

  • No Results Returned: Ensure that your criteria correctly specify existing data.
  • Incorrect Ordering: Verify that the QueryOrder is set correctly.
  • Query Execution Errors: Check for typos in attribute names and ensure that related entities are properly set up.

Next Steps

Now that you have learned how to perform compound queries, you can explore more advanced querying techniques: