One of the most powerful commands in FlexSim is the query() command. It allows you to run SQL-like queries on virtually any data structure in the tree. If you don't know what that means, or what SQL is, or why it would be good to have SQL in FlexSim, this article is for you. If you have general ideas, but you would like to know more, then this article is for you.
This article is not about connecting FlexSim to an external database. If that is your goal, then this article is not for you.
Basics - Tables
Data is often arranged in a table, which very loosely means that data is organized into rows and columns. However, SQL (and therefore the query() command) was designed to work only on certain kinds of tables, that look something like this:
| Name | Age | Fur Color |
| Scooter | 6 | Brown |
| Spot | 3 | Brown |
| Whizby | 4 | Black |
Notice the following points about this table:
- Each column has a unique name
- There are no row headers (although if your table in FlexSim has data in the row header, that's okay; we'll get to that later)
- The table is a list of things, and each row in the table represents a single thing.
- The columns tell you the data that is stored about each thing. In this table, each thing is a pet, and each row stores the name, age, and hair color (the columns) of each pet. If our data included another pet, it would make another row in the table. In database lingo, a row in a table is called a record.
One other thing you need to know about this table is that it has a name (in this case, "Pets"). All tables in SQL have a name, and follow the format described previously.
Basics - Tables In FlexSim
FlexSim has several different ways you can store data. Most users employ Global Tables and Lists (found in the toolbox) to manage data in their model. More advanced users sometimes create tables or bundles on labels. Then there's the model itself, which stores your object's locations in a tree data structure. All the data about where and object is, and how big it is, is stored in the model tree.
So which of these data structures meet the standards for a SQL table? As far as the query() command is concerned, all of them do. The query() command is flexible enough to handle any data structure. The trick is to imagine your data as a table. To do this, you need to be able to answer the following questions:
- How many rows are there?
- What columns will the table have?
- How do you get the data for each cell? Phrased another way, given a row and a column, how do you put data in the table cell?
If you can answer these questions, you can use the query() command. Let's apply these questions to two data structures in FlexSim: the Global Table, and the Global List.
For the Global Table, the answers are very straightforward, but follow along to get the hang of it:
- Q: How many rows are there? A: The number of rows in the Global Table.
- Q: What columns with the table have? A: The same columns as in the Global Table.
- Q: How do you get the data for each cell? A: Given the row and column, look up the value in the Global Table
For the Global List, things get a little more interesting:
- Q: How many rows are there? A: The number of entries on the list.
- Q: What columns with the table have? A: There is a value column, and there is a column for each field on the list.
- Q: How do you get the data for each cell? A: Remember that each row represents an entry on the list. For the value column, I get the value that was pushed on to the list to create the entry. For each field column, I get the value of the field for that entry.
For example, suppose you have this list:
Then supposed that you pushed a 5, 23, -12, 7, and 2 on to this list. When you view the entries on the list, you can see the data on this list laid out as a table:
Notice that the value column has the values that were pushed on the list. The field columns (Doubled, Tripled, OddOrEven, and IsNegative) are each filled with the values of that field.
The query() command has built-in support for Global Tables and Global Lists. However, it is always important to remember that to the query() command, the data is laid out in a table. If the data is not laid out in a table, you can still use the query() command, but you will need to use the methods explained in the Explicitly Defined Tables section.
Basics - Queries
SQL stands for Structured Query Language, so we can guess that it is a language for making queries. So what is a query? In a general sense, a query is a question. In SQL, a query is a question with two parts: who are you asking, and what do you want to know?
There is a general answer to both parts of the question, and this is where tables come in. The answer to the first part (who are you asking?) is always a table. A SQL query is always directed at one (or more) tables. The answer to the second second part (what do you want to know?) is usually a new table. A SQL query takes in a table, and gives back a new table.
The power in SQL comes from the new tables you can make. You can make a table that is a sorted version of the old table, like sorting data in excel. For example, if you have a list of items that need to be processed, you can sort that list by the item's priority. You can also make a table that is a filtered version of the old table, meaning you only kept certain rows. For example, if certain operators can only do certain tasks, you can make sure to only include the tasks that operator can do. In fact, you can make a table that is filtered, sorted, and modified in several other ways all with one query statement.
Basics - Queries in FlexSim
Let's look at examples of the query() command. You can follow along using the attached model ( numberlist.fsm). That model has a script that pushes values on to the list, and then queries the list. It also puts the result into the Global Table called QueryResult. Let's look at the line of that script with the query() command:
int newTableRows = query("SELECT value FROM NumberList");
This line has a lot of new material. You can see that the query() command takes at least one argument, which is the text of a SQL query. We'll discuss the query itself in a moment. The query() command takes in the SQL query, and creates an internal (and invisible) temporary table that contains the result of the SQL query. It then returns the number of rows in the temporary table.
Since you can't readily see the new table made by the query() command, you may want to copy the temporary table to a global table, using the dumpquery() command, as the script does:
dumpquery(reftable("QueryResult"), 1);
The temporary table lasts until you run the query() command again.
Now for the query itself. You can see two parts of this query. The question "What do you want to know?" is answered by the SELECT statement. The question "Who are you asking?" is answered by the FROM clause. In this case, the FROM clause just lists one table, the NumberList. Recall that even though the NumberList isn't technically a table, you can visualize it's data as a table, which is what the query() command does (you can always right-click the NumberList in the Toolbox and choose the "View Entries" option to see the data as a table).
In this example, the SELECT statement lists a column name. This means that the resulting table will have 1 column, because only one is required by the SELECT statement. When you run the script, it will put the result of the query in the QueryResult table:
Notice that this table has only one column, the value column. If you look at the NumberList entries, you will see that this column is identical to the value column in that table.
Examples
To give you a general idea of what you can do with queries, these examples show a few common tasks. The examples all query the NumberList, which can be visualized as the following table:
Selecting Multiple Columns
SELECT value, IsNegative FROM NumberList
Filtering
SELECT value, IsNegative FROM NumberList WHERE IsNegative == 1
SELECT value, OddOrEven FROM NumberList WHERE value >= 5
Sorting
SELECT value, Doubled FROM NumberList WHERE value >= 5 ORDER BY value
Advanced - Callback Values
It is often necessary to create a dynamic query. In FlexSim (not in regular SQL), you can do this by replacing a value in a query with a $n variable. This variable corresponds to additional parameters in the query() command. If you specify $1, then the value in the query will be the first additional parameter in the query() command, $2 will correspond to the second, and so on, up to $9. For example:
query("SELECT Col1, Col2 FROM MyTable WHERE Col1 > $1 AND Col2 < $2", 10, 20)
This query creates a table with Col1 and Col2, but only the rows where Col1 is greater than 10, and Col2 is less than 20. It is possible to include the values 10 and 20 in the query text. However, the $[n] variables allows you to easily change the value in the query, and replace them with more complicated expressions.
Note that using callback values in a query where the FROM value is a Global List is not supported before versions 16.0.6/16.2.1.
Advanced - Explicitly Defined Tables and the $iter() command
Another FlexSim-specific feature of the query() command is the ability to explicitly define a table. This means you can query data that isn't arranged in the table format. In order to do so, you will need to answer the questions posed above, but shown again here:
- How many rows are there?
- What columns will the table have?
- How do you get the data for each cell?
Once these questions are answered, the query() command can treat your data like a table. Each of the following subsections will answer these questions in detail.
Defining the Number of Rows
Let's start with a simple example:
query("SELECT 1 FROM $1", 5)
Notice that we are selecting from a number, not a Global Table or Global List. When the query() command sees a number instead of a table in the from statement, it assumes that you are querying a table with that number of rows. The statement above yields the following table (after a call to the dumpquery() command):
This table has five rows, because $1 (the first additional parameter) evaluates to 5. All the rows have the value 1 because SELECT can handle expressions as well as column names (or expressions involving column names). Note that defining a table will only work if you use callback variables. A from statement like FROM 5 is not valid.
To determine the number of rows in the table, use callback variables in the FROM statement.
Defining the Columns
To define the columns of your table, use the SELECT statement. Each expression in the SELECT statement will result in a column. When you are explicitly defining a table, you will often use callback variables as columns. This is where we can take advantage of the query() command. The query() command is very different from most other commands, because most commands only evaluate their parameters once. The query() command, however, can evaluate its parameters as many times as it needs to. Let's look at another example:
query("SELECT $2 FROM $1", 5, normal(0, 1))
The result of this query is the following table:
Notice that the second parameter, corresponding to $2, was re-evaluated as many times as the query needed it.
Getting the Value in Each Cell
Finally, we need a way to access the correct value for a given row and column. To do that, we need a way to know which row we are currently dealing with. This is where the $iter() command comes in. For example:
query("SELECT $2 FROM $1", 5, $iter(1))
The $iter() command accepts an argument. That argument should match the value of the callback variable, e.g. if you used FROM $1, then the $iter() command should take a one as an argument. The $iter() command is linked to the query() command, so that its return value depends on which row is being evaluated. The above query results in the following table:
Notice that $iter(1) returned the row number for each row.
Now we can begin to query the tree, as if it were a table. Let's start with a simple example. Suppose you wanted a table of all the names of the first-level subnodes of the model tree. Let's start by answering the three questions:
- Q: How many rows are there? A: The number of first-level subnodes of the model
- Q: What columns with the table have? A: A column for the name of each of those subnodes
- Q: How do you get the data for each cell? A: Given the row number, get the name of that object
Let's translate those answers into a query. First, we need to query something that isn't in table format already, so we know we need to use callback variables in the FROM statement:
query("SELECT ... FROM $1", ..., ...)
Next, we need a column for the names of all the subnodes, so we add one to the select statement:
query("SELECT $2 FROM $1", ..., ...)
Next, we need to answer the question of how many rows there are. Since this is the number of subnodes, you can write that as content(model()):
query("SELECT $2 FROM $1", content(model()), ...)
Finally, we need a way to get the name of each of the subnodes. We know that $iter(1) will give us the values from 1 to content(model()), so we can use those values in the rank() command, and we can pass the result of the rank() command to the getname() command:
query("SELECT $2 FROM $1",
content(model()),
getname(rank(model(), $iter(1)))
)
After dragging in a few random objects into the model, you can run that query. If you use the dumpquery() command, you can get a table like this:
In the previous example, we used the value from $iter(1), and passed it in to rank(), and from rank() to getname(), all to get the name of the nth subnode of the model. Since querying a node in this way is fairly common, the query() command also allows you to specify a node as a table, rather than a number. In this case, $iter(1) will iterate over all the subnodes of $1, rather than the values 1 to $1. This makes our query much simpler:
query("SELECT $2 FROM $1", model(), getname($iter(1)))
Advanced - Flattening Data
There is another option for what you can query in a FROM statement. If you use a callback variable (like $1) as your table, then you can use flattening syntax, shown in the following example:
query("SELECT $3, $4 FROM $1x$2", 3, 2, $iter(1), $iter(2))
This query makes the following table:
Notice that for every possible value of $1, there is a row for every possible value of $2. That is the purpose of the x (called the flattening operator); it ensures that there is a row for each permutation of all the callback variables involved. A query containing FROM $1x$2x$3x$4 would produce a table with enough rows for each unique combination of $iter(1), $iter(2), $iter(3), and $iter(4), and then execute the query on that table.
The user manual contains an excellent example of when flattening syntax might be helpful, shown in the help manual in Miscellaneous Concepts > SQL Queries > Example.
