Monday, September 23, 2013

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.

12 comments:

  1. Hello Tom,

    Interesting "old" blogpost. I have downloaded your plugin and tried it.
    Of course I want a more challenging tree (data from three tables: master-detail-detail).
    To have distinct ID fields I have concatenated the ID of the detail table with ID of the master detail
    separated by an underscore.
    This means the ID and the parent ID are text columns instead of numeric columns.

    Your plugin expects numeric values for the both ID columns.
    What will happen if I change the datatype of both columns in the plugin?

    Furthermore we have upgraded the jquery files to 1.11 in our APEX application because of bootstrap.
    This means I interested in a plugin version with a higher jstree version.
    On your GitHub page you mention that you will upgrade the plugin.
    Any ideas when?

    Regards,

    Mathieu

    ReplyDelete
    Replies
    1. Hi Mathieu,

      Thanks for you feedback. This post is indeed a bit "old", and I know I haven't yet blogged about my plugin nor put it on apex-plugins.
      To handle your distinct IDs: I'm not sure why you would have to concatenate ids, your source query probably concatenates and then joins them up by manipulating the values into strings aswell? You could likely write your source query in another way which doesn't create this problem. If you however want to alter the source to accept a varchar instead of a number, that should be no problem.

      As for a newer jstree version: I've actually been looking into it lately. Since jstree v3 requires at least jquery 1.9 I'd have to do some magics to include both a newer jquery version and the jstree version. Most people won't have added a newer jquery version in their apex application, so I'd have to provide a way to both include a new version or skip it in case you already did yourself. Hopefully in the upcoming weeks I'll have an adequate amount of time to actually be looking into it more.

      Delete
  2. Just downloaded your plugins and I will test it with 15000 data loaded into the tree.

    ReplyDelete
  3. Hello Tom,
    I found your blogpost while I was looking for some ideas how to get value of a currently selected node of a new APEX tree (https://community.oracle.com/thread/3876698 not importatnt-just to put it in context).
    It's absolutely amazing and I hope you'll eventually write something similar about the new APEX tree.
    Regards,
    Pavel

    ReplyDelete
  4. Hi Tom, I tried to login with the username and password you provided to view the demo. But it says wrong username and password. Do you an updated password

    ReplyDelete
    Replies
    1. I've changed the authentication on the app (now) to not requiring authentication, as the issues with the user having pw problems were recurring.

      Delete
  5. Hey Tom,
    I have use your plugin and had some change of it. Now it hat a very good UI.
    I have about 60000 Data in my Table.
    The plugin works fine. But I have a problem that, when click a Parent, it take about 1 or 2 Seconds to show all nodes for the Parent. When the parent has just one nodes , it is slow too. I don't know why. Can you give me
    Any advices?

    Thanks
    Pierre

    ReplyDelete
    Replies
    1. Hi Pierre,

      You could try to enable debug and look into that - is it "apex" being slow?
      I'd suspect that the query might not be performing too fast. To get the nodes to load I take the region source sql and apply a "where parent_id = node_to_load_children_for". These nodes are then put in a json string, which is in turn written to the buffer.
      If however SQL nor apex is slow (you can determine that by debugging) then it might be the browser/tree component itself. Is it always slow, even when you only have only the initial nodes showing, or is it only slow after you've loaded in a lot of nodes?

      Delete
  6. This comment has been removed by the author.

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. Hey Tom,

    After Delete a node in a Tree and refresh the Tree, the Initial Loaded Nodes are not refreshed but all others Nodes are refreshed.

    Thanks
    Pierre

    ReplyDelete