Query Builder

◷ Reading Time: 12 minutes

This page is under development.

Query Builder allows building queries without having to manually write the complete queries. Also Query Builder supports reopening already created queries in the same interface.

When you click on each table, it shows the list of columns along with the data types.

How to open the Query Builder

To open the Query Builder, you should first have a Database node on your flow.

After that, follow these steps.

1. Click on the Database node.
2. Click on ... in Queries
3. Click on the Add a new special Item .
4. Select Query Builder from the list.

Enter the connection data

• Provider: Provider of the Server
• Server name: Name of the server
• Authentication type: The authentication type you want to use
• User name: User name of the database connection
• Password: Passowrd of the database connection
• Database: Name of the database

Then the Query Builder will be launched.

Select Tables

To select a table you can,

• Double-click
  or
• drag and drop to add a table

If you right-click on the table, you can rename or delete it.

To include a field in query result, click on the check box.

If you right-click on a field, you can see the options,

• Add to Query/ Remove from Query: Add or remove a field from query results
• Sort Ascending: Sort the results according to ascending order
• Sort Descending: Sort the results according to descending order
• Group by: Group the results by a given field

Click the table list on the left and navigate to Search box on the top. There you can search for the tables.

Join Tables

Join tables help to join multiple tables.

Join Tables Manually

When there is a table already loaded and if you want to join another table to that, you can simple drag and drop the second table. Then the Join Editor will pop up with different options to join the tables.

You can select which join type to be used.

You can also select the table/ column to create the join statement.

Once the join statement is added you can see that the relation is added to the given fields.

If you right-click on the relation,

• Edit Relation: Edit the selected relation
• Delete Relation: Delete the selected relation
• Reverse Relation: Reverse the relation

If you select the edit a relation, you will be given options to edit the join statement.

Join Tables using Suggestions

If you have already selected one table, you can see the suggestions for possible joins indicating the green plus icon. If you hover the mouse over the gree plus icon, you can also see the suggestions.

If you drag and drop another table with possible relation, it will automatically join. If you need to modify that relationship, you can edit that by clicking on the relationship.

You can join multiple tables as follows.

Preview Results

Click on the Preview Results button to preview the results of the built query.

Filter Data

You can filter data using the Filter Editor.

Filter Tab

You can specify the conditions to filter data in the results.

Group Filter Tab

The Group Filter option can be used to specify conditions to filter grouped and aggregated data. This tab is disabled if data is not grouped.

Add a Condition

If you click on Action, you have the options to Add Condition.

Or click on the + icon

You can edit the condition as necessary.

Click on the icon infront of the value type to change the value type. You can add/ retrieve as follows.

• Enter a value
• Select a field from a table
• Enter a parameter

Add Group

The group can be updated as necessary.

You also has the options to select the record limit and select distinct/ unique values.

Edit Parameters

Edit parameters allow adding and removing parameters that can be used in query statements.

You can see the following options in the query parameters window.

• Name: Name of the parameter
• Type: Type fo the parameter value
• Expression: State whether the parameter is predefined value or will be determined dynamically
• Value: Value of the parameter when the Expression option is not checked.

If you have checked the Expression option, you can launch the Expression Editor.

Expression Editor contains a set of built-in functions that can be used ti create expressions.

Shape Data

You can shape the filtered data using the following options.

• Column: The selected column.
  You can choose a column from the drop-down list or check the column in the table.

• Table: The table containing the selected columns from the select statement.
• Alias: The custom column name (only if you have specified any custom name).
• Output: Specifies whether to include the column in the query result.
• Sorting Type: Specifies whether to sort the results according to the selected column (ascending or descending).

• Sort Order: This option is available only after specifying the sorting type.
  It defines the priority of applying the sorting when there are multiple colums with sorting type specified. If you chang one number, the rest of the numbers are changed automatically.
• Group By: Specifies whether to group the query result according to the column.
• Aggregate: Specifies whether to aggregate the column’s data. Applying any of these functions to a column aggregates function result and discards the column results.
  
  The following aggregate functions are available:
  • Count
  • Max
  • Min
  • Avg
  • Sum
  • CountDistinct
  • AvgDistinct
  • SumDistinct