Advanced Lazy Loading using Oracle APEX Tree Region
Jack DroletProblem with the Tree Region
When using a tree in Oracle APEX it can be easy to run into performance problems that don’t exist with other regions displaying the same data. This is because the Tree region will always load every single row returned by the Region Source query, whereas other regions such as Interactive Report or Interactive Grid will only load a page-worth of data and will retrieve the data when the page is changed. This can cause the page to take several seconds to load due to only the tree region loading every single row of data.
We can attempt to address the issue by using the built-in Lazy Loaded flag to allow the page to render before the tree. However, the tree will still load every single node in the tree and take several seconds. This is also not ideal if the tree is central to that page’s utility and there a lot of values in the tree.
What I am going to describe here is a way to only load the root node at page load time, and to dynamically fetch the child nodes of a node only when that node is expanded.
What to do
Below I will use the EMP table as an example on how to set up lazy loading. The users have asked for a tree that shows the employee hierarchy.
Tree Query
In the Tree Region Source, you want to only retrieve the root node of your tree.
Key columns are:
VAL
- The VAL column will be how we pass parameters to the AJAX callback. In this example we will pass the primary key of the table (empno) along with a checksum for security.
LVL
- Will be set to 1 to designate the root as the root
LEAF_STATUS
- Set to 1. Will mark this node as a non-leaf.
select ename as display
, 'fa-user ' as css_class
, 'javascript:alert("' || ename || '");' as link
, empno ||':'|| APEX_UTIL.get_hash(apex_t_varchar2(empno, 'PXXX_EMP_TREE')) as val
, mgr as parent_id
, 1 lvl -- makes it root
, 1 leaf_status -- tells apex that this node is not a leaf
from emp
where mgr is null -- we only get the top level in this query
Tree Region Attributes
Make sure that the following Tree Region Attributes are set.
Node Value Column (VAL)
- Will place this column value into the node.id that we will access when we expand the node. (doc)
Hierarchy (Not Computed)
- Prevents APEX from attempting to build the tree based of the SQL source.
Node Status Column (LEAF_STATUS)
Hierarchy Level Column (LVL)
TreeNodeAdapter
In order to change how the TreeView widget retrieves child nodes, the TreeNodeAdapter must be modified.
childCount function
The childCount function returns the number of children a node has. This is used by APEX when loading the root node. The fetchChildNodes function only runs if childCount returns null. The childCount function that comes with $.apex.treeView.makeDefaultNodeAdapter never returns null so we need to override it.
adapter.childCount = function(pNode) {
if(pNode.children && pNode.children != null) {
return pNode.children.length;
}
else
{
return null;
}
}
fetchChildNodes function
This is the main function that will retrieve the children of a node. Notice how we use the pNode.id to get our parameters to the AJAX callback. We add the dummy node to childNode.children so the toggle that allows the parent to be expanded will be shown.
adapter.fetchChildNodes = function(pNode, pCallback) {
var nodeInfo = pNode.id.split(":");
var empno = nodeInfo[0];
var checksum = nodeInfo[1];
apex.server.process( // ajax to get children of a node
"GET_TREE_CHILDREN"
, {
x01: empno
, x02: checksum
}
, {
success: function(data){
var children = data.children;
pNode.children = [];
for(let i = 0; i < children.length; i++) {
var childJson = children[i];
var childNode = {
id: childJson.id
, label: childJson.label
, icon: childJson.icon
, link: childJson.link
}
childNode.children = [{
label: "Fetching infos"
, icon: "fa fa-refresh fa-anim-spin"
}] // placeholder so that we have the option to expand
pNode.children.push(childNode);
}
pCallback(children.length); // return result to treeview widget
},
error: function( jqXHR, textStatus, errorThrown ) {
pCallback(false);
}
}
);
}
setExpanded function
The APEX Tree region never calls fetchChildNodes again after the initial fetch of the root’s children. Therefore, we also need to call fetchChildNodes when we expand a node. A custom property is added to the node to flag if that parent has been loaded before or not to prevent unnecessary loading.
adapter.setExpanded = function(pTreeId, pNode, pExpanded) {
if(pExpanded && pNode.lazyLoaded != true) { // we fetch async on expansion and it hasnt been loaded yet
adapter.fetchChildNodes(pNode, function(pData){
// get the JQuery object reference of the node for TreeView function calls
var newNode = apex.region(options.regionStaticId).widget().treeView("find", {
depth: -1
, match: function(n) {return n.id == pNode.id;}
});
pNode.lazyLoaded = true; // flag to only load data once
// we have to refresh to see the new children nodes
apex.region(options.regionStaticId).widget().treeView("refresh", newNode);
newNode = apex.region(options.regionStaticId).widget().treeView("find", {
depth: -1
, match: function(n) {return n.id == pNode.id;}
});
// Refresh causes the node to collapse so we expand the node again
// have to find node again because refreshes changes the jquery object
apex.region(options.regionStaticId).widget().treeView("expand", newNode);
});
}
}
AJAX Callback
This AJAX callback essentially becomes the true data source for the tree beyond the root node as we saw for the Tree region source. Note that in this SQL query the VAL column needs to be present and follow the same syntax as the Tree region source so that we can continue to fetch the children of the children.
declare
l_empno apex_application.g_x01%type := apex_application.g_x01;
l_checksum apex_application.g_x02%type := apex_application.g_x02;
l_calculated_checksum varchar2(4000);
l_node_json JSON_OBJECT_T;
begin
l_calculated_checksum := APEX_UTIL.get_hash(apex_t_varchar2(l_empno, 'PXXX_EMP_TREE'));
if l_calculated_checksum != l_checksum then -- security check
return;
end if;
APEX_JSON.open_object();
APEX_JSON.open_array('children');
for node_child in (
select ename as display
, 'fa-user' as icon_class
, 'javascript:alert("'||ename||'");' as link
, empno ||':'|| APEX_UTIL.get_hash(apex_t_varchar2(empno, 'PXXX_EMP_TREE')) as val
from emp e1
where mgr = l_empno
) loop
APEX_JSON.open_object();
APEX_JSON.write('label', node_child.display);
APEX_JSON.write('icon', node_child.icon_class);
APEX_JSON.write('link', node_child.link);
APEX_JSON.write('id', node_child.val);
APEX_JSON.close_object();
end loop;
APEX_JSON.close_array(); --children
APEX_JSON.close_object();
end;
Additional possible features
Conditional expand option
With a bit more work, it is possible to only add the dummy child node when the node has no children. This would make it so the user does not have to press expand before knowing that the node has no children.
Compound keys
Some database structures use a compound key consisting of multiple columns to determine hierarchy. Therefore passing only the primary key (EMPNO) might not be sufficient to determine node identity. In that case it is possible to tweak the pNode.ID (VAL) to use multiple keys by either using a longer colon separated list, or even by packing the info in a JSON string.
Subscribe to my newsletter
Read articles from Jack Drolet directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by