...
In this article, we will be using a custom query with JOIN statement (to get derived data set) to build the data set for the Datalist. The custom query will be placed in JDBC Datalist Database Binder.
The article's discussion and result is produced using MySQL 5.6 but should also be applicable to all RDBMS attached to Joget Workflow.
Let's set the stage first.
...
Depending on which Userview Menu you use, most of them would provide you the capability to show the row count appended to the menu name. The query we have here would take a long time to compute because the database would need to first execute the query (with JOIN) and return the data set entirely before it is able to count the records. The count query will be something like...as the following:-
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT COUNT(*) FROM (SELECT o.*, i.* FROM order o JOIN order_item i ON o.id = i.order_id) temp |
Retrieving derived data and then getting the row count will take anywhere from few seconds to few minutes to load depending on the actual row size and database performance.
This is the explain query result just in case if you are more inclined towards RDBMS kind of explanations.
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | <derived2> | ALL | 2000000 | |||||
| 2 | DERIVED | i | ALL | Index 2 | 2000000 | Using where | |||
| 2 | DERIVED | o | eq_ref | PRIMARY | PRIMARY | 767 | i.order_id | 1 |
As you can see, there are 2 ALL type in retrieving the result. The definition of ALL type is as follow.
| Info | ||
|---|---|---|
| ||
A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked |
Viewing a List in a Userview
...