Build a Query

Follow these steps to build a query:

1. Open the Query Builder

   • Click "Open Builder" in an existing or new query to launch the Query Builder interface.

2. Construct the Query in Designer Mode

   • Use drag-and-drop functionality or double-click elements from the left panel.
   • Add SELECT, JOIN, and WHERE conditions using an intuitive interface.

3. Generate and Validate the Query

   • Click "Generate Query" to automatically structure the SQL statement.
   • The query will be validated against the database.
   • If errors occur, they must be fixed before proceeding.

4. Save the Query

   • Clicking "Save":
     • Validates the query against database.
     • If errors exist, the query will not be saved.
     • If valid, the query is saved.
   • Clicking "Save and Close":
     • Performs the same validation process.
     • If successful, the query is saved, and the pop-up window closes.
   • Clicking "Close":
     • Closes the Query Builder without saving changes.

Query Builder Actions

• Undo / Redo → Revert or restore changes.
• Generate Query → Automatically structures the SQL statement.
• Save → Saves the query after validation.
• Close → Exits the Query Builder without saving.

Design Mode for Type "Select" Query

Select Section

• Users must specify which columns to retrieve.
• Each column requires an alias to prevent conflicts.
• Validation errors occur if:
  • Column names are missing: "This field is required".
  • Alias names are missing or incorrectly formatted: "Invalid Alias Name".

Join Section

• Users can add multiple entities to join tables.
• The Entity Name Dropdown allows selection of entities.
• The Join Type Dropdown offers:
  • Inner Join
  • Left Join
  • Full Join
• Join Entity and Join Entity Attribute must be set for query execution.
• Errors occur if required fields are missing.

Condition Section

• Defines filter criteria to update only specific records.
• Users can add filtering conditions using AND/OR logic.
• Each condition requires:
  • Column Name
  • Operator (Equal to, Greater than, Less than, etc.)
  • Value (e.g., specific numbers, text, or parameters)
• Errors indicate missing inputs for conditions.

Sort Section

• Users can specify sorting columns.
• Sorting ensures query results are returned in a defined order.

SQL Query Preview

• Displays the generated SQL query for execution.

Key Takeaways

• Fix Errors: Ensure required fields (column names, alias names, entity names, conditions) are properly filled.
• Define Sorting: Specify sorting columns to ensure ordered query results.
• Pagination: Use pagination to enhance performance and scalability.

Design Mode for Type "Insert" Query

The Insert Section in the query builder is used to configure INSERT operations, allowing users to add new records to a database table. This section ensures that the right values are provided for each column before executing the query.

Field Configuration

• Each row in the Insert Section consists of:
  • Field Name (Left Side) → Represents the selected DB Entity Attributes.
  • Value Input (Right Side) → Users enter or map input parameter values that should be inserted into the column.

Required Fields:

• Fields (marked with *) must have values before query execution.
• If left empty, validation errors will be triggered.

Optional Fields:

• Fields can be left blank if not mandatory.

Design Mode for Type "Update" Query

Update Section

This section defines which columns in the selected entity should be updated.

Components:

• Add (+) Button → Adds new columns to update.
• Select Column Dropdown → Choose the column that needs updating.
• Value Field → Enter the new value for the column.
• Delete Icon () → Removes the column update from the query.

Join Section

Used when updating records based on related tables.

• Users can add multiple entities to join tables.
• The Entity Name Dropdown allows selection of entities.
• The Join Type Dropdown offers:
  • Inner Join
  • Left Join
  • Full Join
• Join Entity and Join Entity Attribute must be set for query execution.
• Errors occur if required fields are missing.

Components:

• Add (+) Button → Allows adding related entities.
• Entity Dropdown → Selects the entity to join.
• Attribute Dropdown → Specifies the attribute for the join condition.
• Join Type (Dropdown) → Options include Inner Join, Left Join, Full Join.
• Delete Icon () → Removes the join condition.

Condition Section

Defines filter criteria to update only specific records.

• Users can add filtering conditions using AND/OR logic.
• Each condition requires:
  • Column Name
  • Operator (Equal to, Greater than, Less than, etc.)
  • Value (e.g., specific numbers, text, or parameters)
• Errors indicate missing inputs for conditions.

Components:

• Logical Operators (AND, OR, NOT) → Combine multiple conditions.
• Condition Fields → Choose a column, operator (e.g., =, >, <), and value.
• Add Condition (+) → Adds new conditions.
• Add Group (+) → Groups conditions for complex filtering.
• Error Indicator (Red) → Appears when a required condition is missing.

Design Mode for Type "Delete" Query

The Query Builder provides a visual interface to create and execute DELETE queries without writing raw SQL. Below is a breakdown of the sections involved when creating a DELETE query in Query Builder.

Join Section

Used when deleting records based on related tables.

• Users can add multiple entities to join tables.
• The Entity Name Dropdown allows selection of entities.
• The Join Type Dropdown offers:
  • Inner Join
  • Left Join
  • Full Join
• Join Entity and Join Entity Attribute must be set for query execution.
• Errors occur if required fields are missing.

Components:

• Add (+) Button → Allows adding related entities.
• Entity Dropdown → Selects the entity to join.
• Attribute Dropdown → Specifies the attribute for the join condition.
• Join Type (Dropdown) → Options include Inner Join, Left Join, Full Join.
• Delete Icon () → Removes the join condition.

Condition Section

Defines filter criteria to delete only specific records.

• Users can add filtering conditions using AND/OR logic.
• Each condition requires:
  • Column Name
  • Operator (Equal to, Greater than, Less than, etc.)
  • Value (e.g., specific numbers, text, or parameters)
• Errors indicate missing inputs for conditions.

Components:

• Logical Operators (AND, OR, NOT) → Combine multiple conditions.
• Condition Fields → Choose a column, operator (e.g., =, >, <), and value.
• Add Condition (+) → Adds new conditions.
• Add Group (+) → Groups conditions for complex filtering.
• Error Indicator (Red) → Appears when a required condition is missing.

Best Practices for Using DELETE in Query Builder

• Always use WHERE to prevent deleting all records accidentally.
• Use Joins carefully to avoid unintended deletions.
• Preview the generated query before execution.
• Test with a SELECT query first to verify the affected records.
• Use transactions if you need rollback capability.