Create Queries

Steps to add a new query

1. Navigate to Entity Explorer.
2. Right-click on Queries.
3. Select Add to create a new query.

Configure Query Properties

Property	Description	Notes
Data Source	Select the default or external database.	Pulled from database settings.
Name*	Define a unique name for the query.	- Must start with a character, followed by alphanumeric or underscore _. - Cannot start with a number or contain spaces.
Type*	Choose the type of query: Insert, Select, Update, Delete	Determines the behaviour of the query.
Mode*	Select query creation mode: Design Mode → Uses a visual interface. Code Mode → Allows writing raw SQL.	Default mode is Design Mode.
Entities*	Select the entity for the query.	Visible only for Insert queries.
Query Output Type*	Defines the query result format: - Multi Row, Single Row, Single Value, JSON Object, JSON Array (For Select Queries) - None, Boolean, Rows Affected (For other query types)	Controls how results are returned.
Data Type*	For the single value output type.
Pagination	Enables LIMIT/OFFSET to fetch data in chunks.	Only available for Multi Row
Description	Provide a summary of the query.	Helps with documentation.
Is Active	Marks the query as active or inactive.	Default: Yes.

Note: Fields marked with an asterisk (*) are mandatory.

After entering the properties, click the Save icon to confirm the settings. If successful, a message "Query added successfully" appears.

Open Query Builder

1. In the Explorer Menu, locate the newly created query.
2. Double-click the query.
3. In the Properties Panel, click Open Builder.

This opens the Query Builder pop-up window, where the query is constructed visually.

Structure of a Query

Entities

Displays entities that were added during query creation.

These entities define the tables or data sources being referenced.

Input Parameters

Lists all input parameters required for the query.

Parameters act as placeholders for values that the query will use when executed.

If entities or input parameters have been added, they will be visible under their respective folders in the Explorer Panel.

Adding Input Parameters

Steps to Add Input Parameters

1. Right-click the Input Params folder.
2. Select Add → A pop-up window appears.
3. Configure the required properties as described below.
4. Click Save to finalize the input parameter.
5. A success message will appear upon successful addition.

Input Parameter Properties

Property	Description	Comments
Parameter Name*	The name of the parameter.	- Must be unique within the query. - Follows naming conventions (no spaces, special characters except _).
Data Type*	Defines the parameter's data type.	Options: String, Integer, Double, Boolean, Long, DateTime, Binary
Date Option*	Defines a fixed or current date for the parameter.	Only applicable if DateTime is selected as the data type.
Description	A brief summary of the parameter's purpose.	Useful for documentation and maintenance.

Note: Fields marked with an asterisk (*) are required.

Output Types for "Select" Type

Multi Row Query

A Multi Row query retrieves multiple records from the database using a SELECT statement. This query type is ideal for fetching lists of data, such as user records, orders, or transactions.

Key Features:

• Executes a SELECT query to retrieve multiple rows from the database.
• Supports JOIN and SELECT operations across multiple entities.
• Allows fetching data from related entities in a single query.
• Pagination can be enabled for optimized performance.

Configuration Steps:

1. Selecting Multi Row Output Type
   • Set Query Output Type to Multi Row.
2. Adding Entities
   • Click the Add Entity button to include one or more entities in the query.
   • A form appears with the following options:
     • Entity Name Dropdown: Choose the entity from the available DB entities.
     • Alias Textbox: Define an alias for the entity in the query.
3. Pagination Handling
   • Pagination is enabled by default when Multi Row is selected.
   • It is recommended to keep pagination enabled for better performance.
   • Once enabled, offset and page size are automatically added as input parameters.

Use Cases:

• Fetching a list of users, orders, or products.
• Retrieving transaction history from a database.
• Displaying paginated search results in an application.
• Combining data from multiple entities using JOIN operations.

Single Row

The Single Row query type is used to retrieve a single record from the database. It is commonly used when querying data where only one result is expected, such as fetching details of a specific user, order, or record.

Functionality

• Executes a SELECT query that returns only one record.
• If multiple records match the query conditions, only the first one will be retrieved.

How to Configure a Single Row Query

1. Selecting the Query Output Type
   • Choose Single Row in the Query Output Type dropdown.
2. Adding Entities
   • The Add Entity button becomes available.
   • Clicking Add Entity opens a dropdown (Entity Name Dropdown) where you can:
     • Select an entity from the available DB entities.
     • Assign an alias (via the Alias textbox) to refer to the entity in the query.
3. Joining Multiple Entities (Optional)
   • Multiple entities can be added for JOIN and SELECT operations.
   • This allows fetching data from related entities in a single query.

Use Cases

• Fetching user details based on UserID.
• Retrieving a single order using OrderID.

Single Value

A Single Value query retrieves a single data value from the database. This query type is commonly used for calculations, aggregations, and fetching a specific field value from a dataset.

Key Features

• Executes a SELECT query that returns only one value from the database.
• Supports JOIN and SELECT operations across multiple entities.
• Used for aggregate functions such as COUNT(), MAX(), SUM(), AVG(), etc.
• Allows return data type selection based on the query result.

Configuration Steps

1. Selecting Single Value Output Type
   • Set Query Output Type to Single Value.
2. Adding Entities
   • Click the Add Entity button to include entities in the query.
   • A form appears with the following options:
     • Entity Name Dropdown: Choose the entity from the DB Entities.
     • Alias Textbox: Define an alias for the entity in the query.
   • Multiple entities can be added to perform JOIN operations.
3. Return Data Type Selection
   • The return data type is selected when configuring the query in the Properties Panel.
   • Available data type options include:
     • Integer
     • String
     • Boolean
     • Decimal

Use Cases

• Retrieving total number of users using COUNT(*).
• Fetching the highest order value using MAX(order_total).
• Calculating average sales revenue using AVG(sales_amount).
• Getting the latest login timestamp from a user table.

JSON Object

A JSON Object query retrieves a single record from the database, formatted as a structured JSON object. This is useful for representing data with parent-child relationships in a hierarchical format.

Key Features

• Executes a SELECT query that retrieves one record from the database.
• Supports JOIN and SELECT operations across multiple entities.
• Returns data as a JSON object, making it ideal for APIs and structured responses.
• Supports parent-child relationships in a hierarchical format.

Configuration Steps

1. Selecting JSON Object Output Type
   • Set Query Output Type to JSON Object.
2. Adding Entities
   • Click the Add Entity button to include entities in the query.
   • A form appears with the following options:
     • Entity Name Dropdown: Choose the entity from the DB Entities.
     • Alias Textbox: Define an alias for the entity in the query.
   • Multiple entities can be added to perform JOIN operations.
3. Structuring Parent-Child Relationships
   • When multiple entities are added, the result is returned in a hierarchical JSON format.
   • The parent entity represents the primary record, while related child entities are nested inside it.

Use Cases

• Fetching a user profile with related address and contact details.
• Retrieving an order record with customer and item details in a single query.
• Displaying company details along with departments and employees as a structured JSON response.

JSON Array

A JSON Array query retrieves multiple rows from the database, formatting the results as a structured JSON array. This is useful for handling lists of records in APIs and structured responses.

Key Features

• Executes a SELECT query that returns multiple records from the database.
• Supports JOIN and SELECT operations across multiple entities.
• Returns data in a JSON array format, making it suitable for API responses.
• Provides a structured representation of multiple records.

Configuration Steps

1. Selecting JSON Array Output Type
   • Set Query Output Type to JSON Array.
2. Adding Entities
   • Click the Add Entity button to include entities in the query.
   • A form appears with the following options:
     • Entity Name Dropdown: Choose the entity from the DB Entities.
     • Alias Textbox: Define an alias for the entity in the query.
   • Multiple entities can be added for JOIN operations.

Use Cases

• Retrieving a list of users in JSON format for API responses.
• Fetching product details from an inventory database.
• Displaying transaction records with associated metadata.
• Returning customer orders as an array of JSON objects.

Output Types for "Insert" Type

The INSERT query is used to add new records or data entries to a database. It specifies the target entity (table) and provides values for each column in the new record.

Entity Selection

• Users must select the entity where the new data will be inserted.
• A dropdown list displays available DB entities.
• The Entities selection is only visible when 'Insert' is chosen as the query type; otherwise, it remains hidden.

Query Output Type Options

• None – The query executes without returning any value.
• Boolean – Returns true or false indicating whether the insert was successful.
• Scope Identity – Returns the ID of the newly inserted record.

Key Takeaways

• Mandatory Fields – Users must provide values for all required columns.
• Entity Selection – The target entity must be chosen before constructing the query.
• Return Type – Users can select the desired output type for handling responses efficiently.

Output Types for "Update" Type

An Update Query is used to modify existing records in a database table. In the Query Builder, the Update type allows users to configure how data should be updated based on specified conditions.

Query Output Type Options

• None → No output is returned after execution.
• Boolean → Returns true if the update is successful, otherwise false.
• Rows Affected → Returns the number of rows modified by the query.

Adding Entities

1. Entities define the database tables involved in the update query.
   • Click the "Add Entity" button to include entities in the query.
   • A form appears with the following fields:
     • Entity Name Dropdown → Choose the entity from the available database entities.
     • Alias Textbox → Define an alias for the entity to avoid conflicts when multiple entities are used.
   • Multiple entities can be added to support JOIN operations when updating across multiple tables.

Primary Entity

• This field defines the main entity (table) that will be updated.
• The UPDATE statement will target this primary entity.

Output Types for "Delete" Type

A DELETE query in the Query Builder allows users to remove records from a database table based on specified conditions. This is essential for data maintenance and removing outdated or unnecessary information.

Query Output Type Options

• None → No output is returned after execution.
• Boolean → Returns true if the delete operation is successful, otherwise false.
• Rows Affected → Returns the number of rows deleted by the query.

Adding Entities

1. Entities define the database tables involved in the delete query.
   • Click the "Add Entity" button to include entities in the query.
   • A form appears with the following fields:
     • Entity Name Dropdown → Choose the entity from the available database entities.
     • Alias Textbox → Define an alias for the entity to avoid conflicts when multiple entities are used.
   • Multiple entities can be added to support JOIN operations when deleting across multiple tables.

Primary Entity

• The DELETE operation is performed on this entity.
• If JOINs are included, conditions from related tables refine which records are deleted.
• The Primary Entity determines the target table for deletion.