jsTree and APEX
Posted by jholoman on January 9, 2010
I’ve had this post sitting here as a draft since August 4th, but with the arrival of our first son back in June, Ive been a little slow to blog. I was checking out the new early Adopters version of Apex 4.0 and saw the cool tree interface for the app builder. It reminded me of this post. Another reason for not posting was that a new version had been released for jstree with some changes, and I hadn’t had time to make the necessary revisions.
I’ve never really used the built in APEX trees, but I had a need to display some hierarchical data and wanted some added functionality over what the built in apex trees provide. I went out looking for interesting JQuery plugins. The one I settled upon was JSTree . I like that it has a customizable context menu among other things. Well this is just an example and can be made more generic…I’ve hard-coded a couple of things for the purposes of this demo but this should get you started. Here’s the demo: http://tryapexnow.com/apex/f?p=9155:2
For this example I am organizing different types of samples in a case. Here are the tables.
SQL> desc sample_types;
ID NOT NULL NUMBER(38)
DESCRIPTION VARCHAR2(50)
...
TREE_IMAGE VARCHAR2(250)
SQL> desc samples;
ID NOT NULL NUMBER(38)
CASE_ID NOT NULL NUMBER(38)
SAMPLE_TYPE_ID NOT NULL NUMBER(38)
...
PARENT_ID NUMBER(38)
SQL> SELECT s.id, s.parent_id, level
FROM samples s
WHERE case_id = :CASE_ID
START WITH parent_id = 0
CONNECT BY PRIOR s.id = s.parent_id;
ID PARENT_ID LEVEL
---------- ---------- ----------
1067 0 1
1068 1067 2
1076 1068 3
1071 1067 2
1077 1071 3
1078 1077 4
1073 1067 2
1072 0 1
8 rows selected.
Note that I made all parent samples have a parent_id of zero.
The author of JSTree Ivan Bozhanov has done a great job with providing both documentation and examples of how to setup the tree component.
Among the choices of data source, I chose to implement the source as a JSON object, and I wrote a function to output my connect by query in the format that jsTree expects.
create or replace function get_samples_tree(p_case_id_in cases.id%TYPE) RETURN VARCHAR2 AS
l_json VARCHAR2(32767);
l_loop_counter NUMBER(5);
BEGIN
l_json := '[';
FOR i IN (SELECT rownum,
s.id,
s.parent_id,
LEVEL,
lead(LEVEL, 1, 0) over(ORDER BY rownum) lead,
st.tree_image,
st.id type_id
FROM samples s, sample_types st
WHERE case_id = p_case_id_in
AND s.sample_type_id = st.id
START WITH parent_id = 0
CONNECT BY PRIOR s.id = s.parent_id)
LOOP
l_json := l_json || '{ "attributes": ';
l_json := l_json || '{ "id" : ';
l_json := l_json || '"stree_' || i.id || '"';
l_json := l_json || ', "rel" : ';
l_json := l_json || '"' || i.type_id || '"';
l_json := l_json || '}, ';
l_json := l_json || '"data":{ "title" : "';
l_json := l_json || i.id || '", "icon" : "/i/' || i.tree_image ||
'", "attributes" : { "href" : "f?p=' || v('APP_ID') ||
':3:' || v('APP_SESSION') ||
'::NO::P3_SAMPLE_ID:'|| i.id ||
'" }}';
-- l_loop_counter := i.LEVEL - i.lead;
IF i.lead > i.LEVEL
THEN
l_json := l_json || ', "children": [ ';
ELSE
l_json := l_json || '},';
END IF;
IF i.lead < i.LEVEL
THEN
l_loop_counter := i.LEVEL - i.lead;
FOR j IN 1 .. l_loop_counter
LOOP
l_json := TRIM(trailing ',' FROM l_json);
l_json := l_json || ' ] ';
IF (i.lead <> 0 OR j <> l_loop_counter)
THEN
l_json := l_json || '},';
END IF;
END LOOP;
END IF;
END LOOP;
RETURN l_json;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END get_samples_tree;
The on-demand process is straight-forward enough:
declare l_tree varchar2(32767); begin l_tree := get_samples_tree(wwv_flow.g_x01); htp.prn(l_tree); end;
jsTree is quite flexible. In the javascript below, I’m using asynchronous json to get the data. In order to pass the values to the URL in the data.opts section, you use the callback “beforedata”
function populateSamplesTree(pCaseId, pRegionId) {
jQuery("#"+pRegionId).tree({
data : {
type : "json",
async : true,
opts : {
method: "POST" ,
url:"wwv_flow.show"
}
},
callback : {
onchange : function (NODE,TREE_OBJ) {
document.location.href = $(NODE).children("a:eq(0)").attr("href"); },
beforedata : function(NODE, TREE_OBJ) {
return {
p_flow_id:jQuery('#pFlowId').val(),
p_flow_step_id:jQuery('#pFlowStepId').val(),
p_instance:jQuery('#pInstance').val(),
x01:$v(pCaseId),
p_request:"APPLICATION_PROCESS=GET_SAMPLE_TREE" }
}
}
});
//jQuery.tree.reference( pRegionId).open_all();
}
Another way to accomplish the same thing would be to use a “standard” on demand call for APEX, and parse the result as a json object. In order to do that you need the json javascript library from http://www.json.org/
function populateSamplesTree2(pCaseId, pRegionId) {
var get = new htmldb_Get(null,$v('pFlowId'), 'APPLICATION_PROCESS=GET_SAMPLE_TREE', 0);
get.addParam('x01', $v(pCaseId));
gReturn = get.get();
var jsonobj = JSON.parse(gReturn);
apex.jQuery("#"+pRegionId).tree({
data : {
type : "json",
opts : {
static : jsonobj }
},
callback : {
onchange : function (NODE,TREE_OBJ) {
document.location.href = $(NODE).children("a:eq(0)").attr("href");
}
}
});
}
I could store the context menu config in the database as well and generate the JSON for it, but as this is just for example, I’ve left that out. You can easily do dynamic context menus. I’ve included the type in the tree and you can see I’ve hardcoded in Sample Type 1 in the menu option for “split”.
function populateSamplesTree3(pCaseId, pRegionId) {
var get = new htmldb_Get(null,$v('pFlowId'), 'APPLICATION_PROCESS=GET_SAMPLE_TREE', 0);
get.addParam('x01', $v(pCaseId));
gReturn = get.get();
var jsonobj = JSON.parse(gReturn);
apex.jQuery("#"+pRegionId).tree({
data : {
type : "json",
opts : {
static : jsonobj
}
},
callback : {
onchange : function (NODE,
TREE_OBJ) {
document.location.href = $(NODE).children("a:eq(0)").attr("href");
}
},
plugins : {
contextmenu : {
items : {
create : false,
rename : false,
remove : false,
split : {
label : "Split",
icon : "",
visible : function (NODE,
TREE_OBJ) {
if (TREE_OBJ.get_type(NODE) != "1") {
return -1
} ;
},
action : function (NODE,
TREE_OBJ) {
// Just a demo of the arguments
alert('"' + NODE.children("a").text() + '" from "' + TREE_OBJ.container.attr("id") + '"');
}
},
type_2 : {
label : "Another Type",
icon : "",
visible : true,
action : function (NODE,
TREE_OBJ) {
// Just a demo of the arguments
alert('"' + NODE.children("a").text() + '" from "' + TREE_OBJ.container.attr("id") + '"');
}
}
}
}
}
});
}
Now I can just create the regions on the page with source of
<div id="samples_tree"></div> <div id="samples_tree2"></div> <div id="samples_tree3"><div>
Now go time:
<script type="text/javascript">
apex.jQuery(document).ready(function(){
populateSamplesTree('P2_CASE_ID', 'samples_tree');
populateSamplesTree2('P2_CASE_ID', 'samples_tree2');
populateSamplesTree3('P2_CASE_ID', 'samples_tree3');
});
</script>
I’ve left out icons, which are easy to add, and a host of other things. If I ever get some more time I’d like to make this more generic and reusable.
Check out a demo here:
http://tryapexnow.com/apex/f?p=9155:2
