Advanced data Queries #

---

Wizzdi Cloud strives to simplify the backend development process by automating foundational aspects, like creating repositories and generating code for CRUD operations on entities. However, real-world applications often demand more granular control and nuanced data interactions. Recognizing this need, Wizzdi Cloud offers advanced tools such as the custom query builder.

 Automated Entity Management #

• Instant Repository & Service Generation: As you sculpt entities within Wizzdi Cloud, the system recognizes these entities and instantaneously crafts the requisite data repositories and accompanying services to facilitate basic CRUD operations.
• Smart Data Retrieval: Wizzdi Cloud is designed with an innate understanding of entity relationships. For instance, when querying for an entity related to another entity, the system automatically fetches relevant connected data. This feature ensures that data remains contextually rich and relevant. Using the authors and books example, when books are queried, their corresponding author is nested within the retrieved data, providing comprehensive insights without additional requests. This happens only when you retrieve the ‘many’ side of the relation, in this case, the Book. When an Author is retrieved, no book list is populated.

Custom Query Builder #

• Beyond Basic CRUD: While automated CRUD operations suffice for many scenarios, there are times when the default isn’t enough. Whether it’s complex filtering, advanced aggregations, or specific data transformations, there are myriad cases where a more hands-on approach to data access is warranted.
• Intuitive Interface: The custom query builder is designed with user-friendliness in mind. It provides a visual platform to define specific data retrieval conditions, ensuring you get the exact slice of data you need in your desired format.
• Integrated Code & Repository Creation: As with entity creation, Wizzdi Cloud takes over the heavy lifting once you design a custom query. It generates the necessary data repository structures and crafts the service code, ensuring that your custom queries are not just designs but instantly actionable components of your backend.

How To #

General #

A custom query is usually connected to an API endpoint or is called from a Flow. 

These associations are defined using the user interface, if you are interested, like all other elements created in Wizzdi Cloud, and regardless of the deployment method, a custom query becomes generated code behind the scenes. Some of us, sometimes, may want to look at the code, change it, or enhance it. Otherwise, it is just interesting to know.

When we create a custom query, we use the domain model. Custom Queries provide a way to retrieve data beyond the data retrieval provided by the exposed CRUD APIs.

Like the data retrieval services created by the system, data is efficiently fetched using the system’s database engine.

The process of creating a query is usually along these lines:

• Define the entities participating in the query; these must be entities from our project and entities from referenced projects, be it from other workspaces or marketplaces.
• Define the data structure passed to the query; usually, filtering parameters are passed this way. This is called the Request.
• Define the data structure the query returns; this is called the Response. Besides exceptional cases, most queries return a list of the response object type.
• Define the conditions narrowing the result set; for example, fetch all Students studying mathematics.
• Define optional sorting.
• Define optional grouping. Grouping is an essential concept in queries; for example, calculate the number of students in each class passing minimum average exam result.

The User Interface
#

Add Query: To add a new custom query with name and description fields, the name must conform to Java class names.

Manage: edit a query after it is created.

#

Root and Connections (joins):  here, we define what are the entities participating in the query.

Root Entity: In query construction, the root entity is the primary Entity from which the query starts. The central table in your query emanates from all paths (joins) to related tables. Select it by right-clicking on a space on the left side of the screen.

#

Searching for a root entity.

 

Adding entities

Why we need to include other entities via cascaded joins from the root entity:

1. Response Composition: Include related data from different entities in a single query response.
2. Condition Application: Apply conditions involving fields from multiple entities.
3. Result Sorting: Use fields from included entities to sort query results.
4. Data Grouping: Group data by fields from multiple entities in aggregate functions.

Joins are essential when data is normalized across multiple entities, requiring compilation or comparison of information from those entities.

We can use reference fields on the root item to add more entities. (in the image above, we have the Teacher and MainSubject)

Right-click on a reference field (if its type is another entity) and select Join.

 

You can continue adding more entities from the available reference fields on any entity on the diagram.

Note: When one-to-many relations relate entities, we normally find a field of the one side in the many-side entity. For example, a Child entity may have a Mother field in the underlying database, but there is no Children field in the Mother entity.

However, Wizzdi Cloud adds such a List to the one-side entity. This list is never populated, as it can be infinite in size. However, it is readily used in joins; for example, instead of using the TeacherToMainSubject entity as root, use ‘real’ references to add both Teacher and MainSubject entities to the diagram; we can start with either Teacher or MainSubject like this:

#

The request

Every query must define a Request object. This object provides the query with the required parameters affecting the result set.

 

 

 

 

 

 

Define a new request by right-clicking on the right side empty area.

Select Request from the right-side top menu, then right-click to define a request object.

 

 

 

 

The request object includes mandatory fields to control the size of the result set: pageSize and currentPage.

Click the right-facing small arrow to open the Request.

 

 

 

 

 

 

 

Minimal Request Object

Adding fields #

The request object can include nested objects. To define a new object, click the Object button.

 

Using the respective ‘+’ sign, you can keep adding fields at the correct level; when adding a property, you can use the new types you have defined while creating Objects.

The fields from the Request object are usually used in conditions or the response object.

The Response #

Available options when building a response object:

• Create a property; the property has a name and a data source.
• Create an Object accommodating a more complex structure in the response.

#

We can now select the data source for the subjectName property.

 

 

 

 

 

 

 

 

 

While setting the data source for a property.

 

 

 

 

 

Using functions in conjunction with fields.