Implementing an ajax tree in apex

So ajax trees. That sounds great right? To load the data on demand and not in one huge chunk? Honestly, I've noticed slowness/slowdown when there are even just a few hundred nodes around. And why? Often most of the data is never shown or needed, as people implement the link portion in the tree component. As a result, often there is only the selected node's branch and parent branches open. Yet I feel for the tree in apex. It is a component that wholeheartedly needs some love and some expanded options. For example the bundled version of jstree is plainly put ancient. It functions well but it suffers from clarity in documentation and location thereof and actual examples to be found on the net. The newer version, 1.0.0 has been released for a long time now, and while the look and feel can be exactly the same, this time around it has become a lot better on the developer's side of things.
Having said that, I looked at how to implement an ajax tree for both the current bundled version (which as of 4.2.3 is still 0.9.9a2) and using a 1.0.0 version.
Disclaimer though: there is a lot of javascript involved. Honestly, know what you're in for with this. If javascript and ajax aren't hurting your brain by just reading about it then you'll be fine, but otherwise steer clear. Do I say this because this is hard? Not really, personally I think that once you see an example it will slot down easily. But if you're only setting out in apex and only made baby steps in the way of javascript and html then for your own sake do not implement this in any demo or production system. Toy around all you like, but don't implement something for a customer if you can not support it.

And when? I'd say implementing an ajax tree has its uses and place. The downside is that I have not made this (yet?) into a plugin, so there is no declarative apex goodness. Using the standard tree component it will simply all be there. But if and when you start to feel the tree is slowing you and your users down, then the time may have come to look into your options.
So jstree then. I know there are other implementations around or workarounds or anything, but I decided to stick with jstree. It only makes sense that one day we'll have an upgraded version in apex. As I mentioned before: the newer version is tons better, and will allow someone with a grasp of what is going on and some knowledge of javascript to easily implement more features to his tree.

Basic setup

Basically an ajax tree setup is rather simple. To get data there need to be ajax calls. Those calls will require an application process. The process will need to return data in a certain form so that jstree can correctly parse it and create the html.
I also opted to load every node. I could have fed the tree some static data first, but I didn't do that here. You should know that is possible however, and that you could generate your apex page with an initial payload in it, and then do ajax calls from there on. I'll ask you refer to the docs though, and maybe later I may provide an example on that, but you should be able to find out if you understand what I did.

In both cases and on my two example pages I created a standard HTML region with the source set to:

<div id="treecontainer">
</div>

This div will become the container for the tree later on.

Using the standard component bits, jstree version 0.9.9a2

I'll say right off the bat that setting up an ajax tree isn't too hard, but has its limits. I didn't bother with implementing a search functionality on this version because it's way to fiddly and hackery and plainly said a huge waste of time. If you want that then just push on and simply go with the newer version is my advice - you're writing javascript either way.

So let's get on with it. Code first, comments about it below it.

• file includes:

  #IMAGE_PREFIX#libraries/jquery-jstree/0.9.9a2/apex.jquery.tree.js

  This is the jstree file that is also used for the apex tree widget.
• javascript:

  function init_tree_custom(){
    var gTreeConfig = 
      {
        data:{
          type:"json",
          async:true,
          opts:{
            isTreeLoaded:false,
            method:"GET",
            url:"wwv_flow.show"
          }
        },  
        opened: [],
        selected: "",
        rules:{
          valid_children:"root",
          use_max_depth:true
        },
        callback:{
          beforedata: function(NODE, TREE_OBJ) { 
                        return {"p_request"      : "APPLICATION_PROCESS=GET_NODE_DATA",
                                "p_flow_id"      : $v('pFlowId'),
                                "p_flow_step_id" : $v('pFlowStepId'),
                                "p_instance"     : $v('pInstance'),
                                "x01"            : $(NODE).attr("id") || 0        
                               };
                      }
        },
        root:{
          draggable:false,
          valid_children: "folder"
        },
        folder:{
          valid_children: "file"
        },
        file:{
          valid_children: "none",
          max_children: 0,
          max_depth:0
        }
      };
      
      gTreeConfig.opened = ["1","2","5","6","39","43"];
      gTreeConfig.selected = "43";
      
      var gTree = $("#treecontainer").tree(gTreeConfig);
  };
  

  What I did here is just build up a tree configuration object. Plenty of it can just be gleaned from the documentation and also from the apex widget implementation of the tree. For example the settings for root, folder and file are not mine but some I simply copied over. Other parts I left out for practicality's sake.
  So first, "data". This is the basic ajax setup and tells jstree that the tree has to be filled asynchronously with ajax retrieved data in json notation.
  "opened" and "selected" I added more for show. Opened will tell jstree which nodes to open up (and awesomely enough will load as required, though you should take care that parent nodes come before their children!). Selected tells jstree which node is to be selected after all loading has finished (this includes the "opened" list of nodes!). The reason for not outright defining them in the object is to show you it does not have to be done there. You can set the values before the tree is initialized, and thus you could put out some data somewhere to initialize the array with relevant values.
  The callback part is also important. In this object you can define all callbacks that have to be reacted upon. The "beforedata" callback is called before any data is retrieved (ie ajax call). It allows manipulation of what is sent in the ajax call and is vital to the ajax setup. Especially the value in x01 as that is the node to be loaded!
• Execute on page load:

  init_tree_custom();

  This will initialize a jstree instance.
• on demand process: GET_NODE_DATA

  DECLARE
    l_json VARCHAR2(4000);
  BEGIN
    SELECT '['||listagg('{"attributes":{"id":"'||node_id||'"},"data": {"title":"'||name||'", "attributes": {}}, "state":"closed" }',',') within group(order by node_id)||']'
      INTO l_json
      FROM treedata
     WHERE parent_id = apex_application.g_x01;
  
    htp.p(l_json);
  END;
  

  This is another tricky part as you need to know the correct syntax for this. Jstree expects a certain syntax after all! But the core things are there: node id and display.
  The "state: closed" is CORE to the solution and what makes it tick. The reason for this is that these nodes are added to the tree but in a closed state. A closed state indicates to jstree that the node has not had its contents loaded yet - and jstree does not load an opened node again unless specifically asked to!
  A worthy point of attention is that listagg has a limit of 4000 characters. If your data will go over that (a real humongous tree with loaaaaads of nodes as children for example) there is no other option than either emitting a clob split in pieces or emit object per object.

  If you must know why the search is so annoying to get right it is because it uses the same data-fetching process used for node retrieval, and automatically adds a parameter to the query string. Unfortunately there is no way for us to access that extra parameter in plsql as we can only use the standard apex set of items, arrays and temp items. There is no real way to intercept and manipulate this call without outright hackering around. Which is not my goal. If you really must do with this component I advise to not use the tree search but simply implement an own solution and document that well, instead of something trying to get the tree component to play nice. Again: if you'd try to implement a search such as I'd described in a previous post then be aware that it will actually perform an ajax call and it just won't work.

• demo page

Implementing the newer version: jstree v1.0.0

Since apex 4.2 (I suppose, but do not remember, 4.1 too) uses jQuery version 1.7 we can not use the last version of the tree, since that has been adapted to jQuery 1.8+ (fun fact: jquery by now is 1.9 and 2.0!). So I went into the Github archive of jstree and fetched the version prior to the 1.8 implementation and used that, but you can just use rc2 of the tree.
For the example's sake on apex.oracle.com I had to upload some files to the static files of the application.
I also opted to use the jQueryUI styled theme of the tree instead of the default tree look and feel. I further enhanced it by implementing some used-to functionality: loading icon and the dots in front. I found the tree looked just a bit too bland without it.

• javascript file includes:

  #WORKSPACE_IMAGES#jquery.jstree.pre1.8.js

  This includes the jstree javascript, the version I fetched before the library got updated to 1.8.
• javascript:

  function init_tree_custom(){
    var lTreeConfig = {
      "plugins" : ["json_data", "search", "ui", "themeroller"],
      "core" : {
        "load_open": true,
        "initially_open": []
      },
      "ui" : {
        "initially_select": []
      },
      "json_data" : {
          "ajax" : {
              "type"           : 'GET',
              "url"            : "wwv_flow.show",
              "data": function(node){
                        return {
                          "p_request"      : "APPLICATION_PROCESS=GET_NODE_DATA",
                          "p_flow_id"      : $v('pFlowId'),
                          "p_flow_step_id" : $v('pFlowStepId'),
                          "p_instance"     : $v('pInstance'),
                          "x01"            : $(node).attr("id") || 0,
                          "x02"            : "LOAD"
                          };
              },
              "success": function (new_data) {
                  return new_data;
              }
          }
      },
      "search":{
        "ajax" : {
              "type"           : 'POST',
              "url"            : "wwv_flow.show",
              "data": function(searchvalue){
                        return {
                          "p_request"      : "APPLICATION_PROCESS=GET_NODE_DATA",
                          "p_flow_id"      : $v('pFlowId'),
                          "p_flow_step_id" : $v('pFlowStepId'),
                          "p_instance"     : $v('pInstance'),
                          "x01"            : searchvalue,
                          "x02"            : "SEARCH"
                          };
              },
              "success": function (nodelist) {
                  return nodelist;
              }
        }
      }
    };
  
    lTreeConfig.core.initially_open = ["1","2","5","6","39","43"];
    lTreeConfig.ui.initially_select = ["43"];
  
   $("#treecontainer")
     .bind("select_node.jstree", function(event, data){console.log(data);})
     .jstree(lTreeConfig);
  }
  

  Oh man - where to start!? I strongly advise to take a look at the official documentation: start with the core functionality. I really found it plenty helpful. It took a bit for me to figure everything out but not that hard.
  First of all, the plugins. "json_data" since I want to pass data in using json notation. "search" because I want to search the tree. "ui" because I want node selection. "themeroller" because I want the tree to use jquery-ui theming.
  The json data definition says it all: use an ajax call to retrieve node data. x01 will hold the node to look up data for (important: 0 if root node!). Since I've also defined ajax for the search plug-in, I decided to call the same process but with x02 set to a different value.
  
• Execute on page load:

  init_tree_custom();

• css file includes:

  #IMAGE_PREFIX#libraries/jquery-ui/1.8.22/themes/base/jquery-ui.css

  This includes the only apex-bundled jquery-ui theme, base. It's grey. But serves the example.
• css inline:

  /*Constrain the tree height a bit, helps make it less jumpy*/
  div#treecontainer{
  height: 400px;
  overflow-y: scroll;
  }
  
  #treecontainer ins{
  background-color: transparent;
  }
  
  div.jstree li > a.jstree-search{
  background: #CCCCCC;
  }
  
  /*
   * jsTree default theme 1.0
   * changed, adapted and cut, to work with workspace images for demo and improve look-n-feel
   */
  .jstree li, 
  div.jstree li > ins.jstree-icon
   { background-image:url("#WORKSPACE_IMAGES#d.png"); background-repeat:no-repeat; background-color:transparent; }
  .jstree li { background-position:-90px 0; background-repeat:repeat-y; }
  .jstree li.jstree-last { background:transparent; }
  .jstree .jstree-open > ins { background-position:-72px 0; }
  .jstree .jstree-closed > ins { background-position:-54px 0; }
  .jstree .jstree-leaf > ins { background-position:-36px 0; }
  
  .jstree a.jstree-loading .jstree-icon { background:url("#WORKSPACE_IMAGES#throbber.gif") center center no-repeat !important; }
  

• on demand process: GET_NODE_DATA

  DECLARE
    l_node treedata.node_id%TYPE;
    l_json VARCHAR2(4000);
  BEGIN
    IF apex_application.g_x02 = 'LOAD' THEN
      SELECT '['||listagg('{"data":"'||name||'","attr":{"id":"'||node_id||'"},"state":"'||CASE is_leaf WHEN 0 THEN 'closed' ELSE 'open' END||'" }',',') within group(order by node_id)||']'
        INTO l_json
        FROM (select level lvl, node_id, parent_id, name, connect_by_isleaf is_leaf
                from treedata
             connect by prior node_id = parent_id
               start with node_id = 1
             )
       WHERE parent_id = apex_application.g_x01;
  
      htp.p(l_json);
    ELSIF apex_application.g_x02 = 'SEARCH' THEN
      BEGIN          
        select '['||listagg('"#'||replace(ltrim(SYS_CONNECT_BY_PATH(parent_id, ','),','),',','","#')||'"', ',') within group(order by 1)||']' node_path
          into l_json
          from treedata t
         where upper(name) like '%'||UPPER(apex_application.g_x01)||'%'
        connect by t.parent_id = prior t.node_id
        start with t.parent_id = 0;
              
        htp.p(l_json);
      EXCEPTION WHEN NO_DATA_FOUND THEN
        htp.p('[]');
      END;
    END IF;
  END;
  

  Now here happens a lot of magic.
  • Start off with the LOAD part:
    First of all I'm always selecting from the tree structure as you can see in the sub-query. I do this because I wanted to leverage connect_by_isleaf because this will return 0 when there are children for this node. I use this in the query on it, where I check and set the state of the node to open or closed. The why to it is that if you load each node as closed, then each node will be expandable in the tree simply because it is not known whether or not there are children for it. It rather ticked me off to not be able to visually tell if a node has children or not, and thus would require me expanding each node to know. So I decided to drive this behaviour from the query, where I can easily pick this up. Leaf nodes will load in as actual leaf nodes and will not be expandable.
  • SEARCH:
    Performing a search on the tree with ajax will expect back a full path of nodes to open up. It also expects you to set up the search pattern. Here I just use a case-insensitive search on name of my nodes, but you can set it up any way you want obviously.
    Every node matching the search criteria is filtered out, and the path is returned by sys_connect_by_path. Note the specific mark-up: what is actually being returned is a list of id selectors ("#1","#2","#5" etc).
    Now the attentive reader will probably have noticed that this query will return the same nodes several times. Eg if 2 children of the same parent would need to be returned, then that parent node will be twice in the result set. To be fair, that is a concession I made to keep it clear and not to clash with how the tree opens nodes. As with the "initially_load" setting, nodes are opened as the structure expands. This means that that parent has to be loaded before a child [that has to be opened] can be loaded. However, already opened nodes will just be skipped. So paths to retrieved nodes have to be in descending order. But writing a query which would filter out doubles while actually keeping that sorting went a bit too far for me. I'm not phased by putting doubles in there, it's handled fast enough.
• demo page
• jstree documentation page: http://www.jstree.com/documentation
• Again I'd like to point out that the new version of the tree is a lot more flexible and gives a lot more ease of configuration than the current version in apex. Checkboxes? Plug it in. Drag and drop? Plug it in. Rename nodes? Yes, plug it in. Of course you'd need to bind to the events and use ajax to push it to the database - like before - but actually setting that up is a lot easier to do.

In closing

The demo application (link, again) can be downloaded (link on its homepage) and has everything in it you need. The table with seeding of data is in it, and the files for the tree are in the static files of the application. This will allow you to more closely inspect how I set it up and make it easier to reproduce.

Why is the tree slow?

An explanation of what goes on with the tree component in apex and why it grows increasingly slow as more data is presented.

I've speculated and written down some about this here and there, but haven't ever gotten into much detail of it. Partially because I didn't have the time and partially because I was confused about it. When people ask about the tree being slow, it always has to do with the amount of data in it. Let's say 6000 records. That is a lot of data though, and so beside the technical why-is-it-slow you have to consider that that is just a lot of data. Nevertheless, it's understandable to say that control over that can be a bit out of your hands. After all, you simply want to display hierarchical data and then preferably correct - so no arbitrary leaving out of records.

What actually happens

When you define a tree component you provide the query to it that will supply the data. The tree component is based on the jQuery plugin jsTree. It can work with several types of datastores, and the apex team has decided to work with a json container. This container is built up according to the query and settings in the tree component, and it will create one giant json object. This is then emitted in script tags in the tree region, and assigned to a global variable. The structure is not flat either, but objects nested in objects to represent the tree structure. Thus, a root node will have a children object, and those children will have children objects again etc.
Already you could imagine that having one such huge object in javascript would be taxing on the memory of the browser.

When the page has loaded however, you will not see any json objects. Rather, you will have a functional tree. JSON data is not just presented, that is what HTML is used for. Evident when looking at the code with dev tools: you will find an unordered list (UL) structure. This code is not generated (emitted) by apex however, it is generated at load (runtime) by the jstree plugin. Since the tree has to use the json-object with all nodes, it will transform that object into a presentable structure, and thus it will perform a lot of DOM manipulation: creating elements and attaching them to the document. This is probably the most taxing part, and why some browsers deal better than others with it.
Of course, what makes it worse is how undynamical this setup inherently is. The node selection will cause a page redirect, meaning that if the tree is to be shown on that other page it has to be rebuilt all again - even if all you want to do is refresh a report to the right of it. It is not out of the box refreshable of very interact-able with dynamic actions, but requires some understanding of html/javascript to get things working.

Apex so far has used version 0.9.9a2 of jsTree for its tree component, and this version has actually been outdated for quite some time. It does have documentation but nowadays it can be hard to find examples of implementations, which hasn't helped in deciphering everything. In hindsight, when I may have defended the tree component and said there is documentation, it is a bit obscured and put to the side and it could've all gone a bit clearer. The tree is, probably - in my book, one of those components that simply haven't been touched on for a while because it "may" be sufficient for what most want to achieve with it: simple data presentation with not too much data.

Basically, the best solution for when you want to use the tree but don't want an unnecessary huge load on that page then using an ajax-fed tree is ideal. But not very much out-of-the-box, although I found this to be pretty easy to implement. Personally I've looked at the 1.0.0 version of the tree plugin, found this to be a huge improvement, and then went on to implement it. It's rather easy and painless - but I'll handle that in the next post.