Query Constructor

With the Query Constructor, you can visually construct queries to the built-in AnyLogic database. There is no need to write SQL queries manually; just construct the query graphically using controls and AnyLogic will automatically construct the corresponding query and insert into the current expression.

The example below queries the database of the Corporate Education model.

To insert a query to the AnyLogic database

1. In the Properties of an element, click inside the code field where you want to place a query. This can be any code field: a block action, an agent action, or simply a button.
2. Click the Insert Database Query... toolbar button.

   

3. The Insert Database Query dialog box appears:

   

   From the Table list, select the table of the AnyLogic built-in database table that you want to query.
4. From the Value column drop-down list, select the column of this table that contains the data you need.
5. In the Choice conditions section below, define the conditions that specify which particular values you want to select from the database table.
   Select the table column from the drop-down list. In the field below, specify the value that you are searching for in the specified table column (in the example shown in the figure, we are searching for "Credit Specialist" values in the employee_type column).
6. If necessary, you can add more selection conditions. Add a new section to define another condition by clicking the button.
7. The Query Constructor allows you to construct any query. In the Choose action list of options, the Constructor proposes a list of the most common actions:
   • Single value — After evaluation, the query is replaced with the first value from the database that matches the specified conditions. This is useful when you expect only one result or only need the first matching entry. For example, you can retrieve the name of a specific employee when their ID is provided.
   • List of values — After evaluation, the query is replaced with a list of values that match the specified conditions. This is useful when multiple records meet the criteria, and you need to collect all the relevant values. For example, you can get a list of all employee names that are "Credit specialists".
   • Count rows matching the given condition(s) — Returns the number of rows that match the specified conditions. This option is used when you need a count rather than the actual data, like counting the number of employees with the type "Credit specialist".
   • Check if any rows match condition(s) — Determines whether any rows in the database match the specified conditions. This is a true/false check to see if there are any matching records. For example, this way you can check if there are any employees with the specified job title.
   • Select table records — Selects and retrieves full table records that meet the specified conditions. This option is used when you need detailed information from the matching records. For example, you can fetch detailed information (name, department, salary) of all employees who are credit specialists.
   • Iterate over returned rows and do something — Execute an action for each row that matches the conditions. This is useful for performing operations or transformations on each matching record. For example, you can use this to update the status of all "Credit specialists" to "Active" in a batch process.
8. In the Preview field you can see the constructed query. Queries are built in two syntaxes: SQL and Java API (Query DSL). You can use any syntax in your models. Try switching between syntaxes in the Query Constructor dialog (Syntax option) and compare the resulting queries, so you can understand the Query DSL query syntax.
9. When finished, click the OK button. You will see the query inserted in the code field that you used to call the Query Constructor wizard. If needed, you can edit the query there.

How can we improve this article?

Send