Visual Query Builder - SELECT Query Generation

The Visual Query Builder provides an intuitive way to build SQL SELECT queries by visually interacting with tables and relationships in your database. This guide explains how to create and customize SELECT queries.

Steps to Create a Visual Query

Begin by entering the Query Name in the input field.
Choose the Query Type as SELECT. (The options include: Select, Insert, Update, and Delete).
Select the Connection to the appropriate database (e.g., EmployeDetailsdbDevelopment).
Optionally, provide a description for the query under the Description field.

Table Selection and Joins

The Table Selection and Joins feature in the visual query builder allows users to easily select tables from the connected database and create relationships (joins) between those tables to generate SQL queries. This visual interface simplifies working with multiple tables and creating complex queries using drag-and-drop functionality.

Table Selection:

After connecting to a database, the visual interface will display all available tables. You can drag and drop the required tables into the canvas area to start building your query.
Example: In the image, two tables (adminuser and aiassistant) are added. The adminuser table contains fields like id, pk, email, and isadmin, while the aiassistant table contains fields like id, companyid, and projectid.
Each table shows its fields (columns) that you can select or deselect, depending on which data you need for your query.

Creating Joins:

Once the tables are added, the system will automatically suggest joins between tables based on foreign keys or defined relationships.
In cases where there is no predefined relationship, you can manually create joins by selecting common columns between the tables.

Inner Joins:

By default, an inner join is created if there is a matching column (e.g., id) between the tables. This means that only records with matching id values from both tables will be included in the result.
Example: In the above image, the adminuser and aiassistant tables are joined on the id field, forming an inner join between these tables.

Adding Joins:

If you need to create additional joins or join on other fields, you can manually add more joins using the Join Query interface.
Example: You can add multiple joins to connect other columns like companyid or projectid from one table to the corresponding columns in another table.

Join Types:

You can specify the type of join (e.g., INNER JOIN, LEFT JOIN, RIGHT JOIN) depending on the relationship you want to define between the tables. This allows for more control over the query.
- Inner Join: Returns records that have matching values in both tables.
- Left Join: Returns all records from the left table and matched records from the right table. If no match, NULL values are returned for columns from the right table.
- Right Join: Similar to Left Join but returns all records from the right table and matched records from the left table.

Example of a Join Query:

In the visual query builder, the following join query might be generated:

SELECT adminuser.id, adminuser.pk, aiassistant.id, aiassistant.companyid
FROM adminuser
INNER JOIN aiassistant ON adminuser.id = aiassistant.id;

This query selects the id and pk columns from the adminuser table and the id and companyid columns from the aiassistant table, joining the two tables on the id field.

Complex Join Queries:

The interface allows you to create more complex join queries involving multiple tables and various join types.
You can add multiple tables and define different join conditions for each one, enabling you to fetch and combine data from various parts of the database into a single result.

Preview Data:

This feature lets you preview the data from the tables you selected.
It’s useful for confirming that the data you're working with is correct before proceeding with the query.

Params Tab:

The Params tab allows you to define any parameters that will be used in the query.
Example: In the given interface, a parameter with the name const 1 is defined with the type string. You can add additional parameters as needed.

Where Tab:

The Where clause allows you to filter the data returned by the query. You can specify conditions for columns in the tables.
Example: A condition is set in the aiassistant table where the id must equal a specific value (e.g., re4wfe4wtfr4ewtfvre54).
This is where you define the logic of the query to narrow down the result set.

Group By and Aggregate in SQL Queries

The Group By & Aggregate feature is essential for summarizing and analyzing data by grouping rows based on column values and applying aggregate functions (e.g., COUNT(), SUM(), AVG()). This feature allows you to generate meaningful insights by calculating aggregated results for each group.

How to Use Group By & Aggregate:

Group By:
- Use the Group By clause to group data based on one or more columns. Each group contains rows with the same value in the specified columns.
- Example:
```
SELECT department_id, COUNT(employee_id)
FROM employees
GROUP BY department_id;
```
  - In this example, employees are grouped by their department_id, and the total number of employees is counted for each department.
Aggregate Functions:
- COUNT(): Counts the number of rows in a group.
  - Example: Count the number of employees in each department.
```
SELECT department_id, COUNT(employee_id)
FROM employees
GROUP BY department_id;
```
- SUM(): Sums up the values in a column for each group.
  - Example: Calculate the total sales per product.
```
SELECT product_id, SUM(sales_amount)
FROM sales
GROUP BY product_id;
```
- AVG(): Computes the average value of a column for each group.
  - Example: Find the average salary per department.
```
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;
```
- MAX(): Retrieves the maximum value within each group.
  - Example: Get the highest sales amount per category.
```
SELECT category_id, MAX(sale_amount)
FROM sales
GROUP BY category_id;
```
- MIN(): Finds the minimum value for each group.
  - Example: Find the smallest order quantity per supplier.
```
SELECT supplier_id, MIN(order_quantity)
FROM orders
GROUP BY supplier_id;
```
Having Clause:
- The Having clause filters the results after the groups have been created, making it ideal for applying conditions on aggregated values.
- Example: Display only the departments where the average salary exceeds $50,000.
```
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;
```
  - In this example, the query shows departments with an average salary greater than $50,000.

Tip: The Having clause is similar to Where but is used after the data has been grouped. Always use Where to filter rows before grouping, and use Having to filter groups based on aggregate functions.

Example of Using Group By & Aggregate:

SELECT product_id, COUNT(order_id), SUM(order_amount)
FROM orders
GROUP BY product_id
HAVING SUM(order_amount) > 10000;

Formula Columns:

This tab allows you to create custom calculated columns using formulae based on existing columns.
Example: You can create a formula to calculate a total value by multiplying quantity and price.

SQL Query Tab in Visual Query Builder

The SQL Query tab is where the actual SQL query is generated based on the tables, columns, filters, and conditions that you defined using the visual query builder. It allows you to see the underlying SQL code for your query and modify it if necessary.

How the SQL Query Tab Works:

Automatic Query Generation:
- When you create a visual query by selecting tables, joining them, applying filters, and using group by clauses, the system automatically generates the corresponding SQL query.
- This is particularly useful for users who may not be fully familiar with SQL but still want to generate complex queries through an intuitive, visual interface.
SQL Query Example:
- Based on the selections in the visual query builder, the SQL query might look like this:
```
SELECT adminuser.id, adminuser.pk, adminuser.email, aiassistant.id, aiassistant.pk, aiassistant.companyid
FROM adminuser
INNER JOIN aiassistant ON adminuser.id = aiassistant.id
WHERE aiassistant.id = 're4wfe4wtfr4ewtfvre54';
```
- This query is an example of joining two tables (adminuser and aiassistant), selecting specific columns, and applying a Where condition to filter the results based on a specific id.
Editable SQL Query:
- While the query is generated automatically, you have the option to manually modify the SQL code directly in the SQL Query tab. This provides flexibility if you need to fine-tune the query beyond what the visual interface allows.
- Example: You can add additional filtering conditions, adjust joins, or change the order of the selected columns directly in the SQL code.
Previewing the SQL Query:
- Once the query is generated, you can see the SQL output directly in the tab. This output is based on the selections, filters, and other options you defined earlier in the visual query builder.
- The query will reflect all of the conditions you applied, such as:
  - Table joins
  - Selected columns
  - Where conditions
  - Group by and aggregate functions

Tip: Even though the system automatically generates the SQL query, you can always edit it manually to customize the query for more advanced use cases. This ensures that both beginners and experienced SQL users can benefit from the visual query builder.

SQL Query Example:

Below is an example of a complete SQL query generated based on your input in the visual query builder:

SELECT adminuser.id, adminuser.pk, adminuser.email, aiassistant.id, aiassistant.pk, aiassistant.companyid
FROM adminuser
INNER JOIN aiassistant ON adminuser.id = aiassistant.id
WHERE aiassistant.id = 're4wfe4wtfr4ewtfvre54';

Validation Result:

The Validation Result tab will show any errors or warnings in the SQL query.
Example: In this case, an error related to the unsupported database type (digisquaresmysql) is shown.
This feature ensures that the query is syntactically correct and compatible with the selected database.

Tips for Using the Visual Query Builder

Drag and Drop: Use the drag-and-drop interface to easily add tables and establish relationships.
Joins: The system automatically handles table joins, but you can manually adjust the join type (e.g., INNER, LEFT OUTER) as needed.
Grouping and Aggregates: If you need to summarize data, use the Group By and Aggregate functionality.
SQL Preview: Always check the SQL Query tab to understand the underlying query that has been generated.
Validation: Before running the query, ensure that the Validation Result tab is free of errors.

By following this guide, you can create powerful SQL SELECT queries without manually writing SQL code. The visual query builder simplifies the process and helps you generate efficient queries using an intuitive interface.

Visual Query Builder - SELECT Query Generation

Steps to Create a Visual Query​

New Visual Query Modal:​

Table Selection and Joins​

Inner Joins:​

Adding Joins:​

Join Types:​

Preview Data:​

Params Tab:​

Where Tab:​

Group By and Aggregate in SQL Queries​

How to Use Group By & Aggregate:​

Example of Using Group By & Aggregate:​

Formula Columns:​

SQL Query Tab in Visual Query Builder​

How the SQL Query Tab Works:​

SQL Query Example:​

Validation Result:​

Tips for Using the Visual Query Builder​