Oracle APEX Tree Search

Implementing search feature for the tree region is little tricky. Consider we have tree region created on the employee table EMP as below.

If we search for employee JAMES and we want to show JAMES node, then we must show everyone up in the hierarchy till the root node, i.e. KING > BLAKE > JAMES. Luckily, we can easily get hierarchy (traverse path) using sys_connect_by_path. 

For e.g. consider below query

 select ename,
        empno,
        mgr,
        sys_connect_by_path(empno, ':')||':' as emp_path
   from emp
  start with mgr is null
connect by prior empno = mgr
  order siblings by ename

This query gives all employee rows from EMP table, along with path data, from the root node to current node. Now, we can use this path data to filter rows that are required to show any node. For e.g. we can use below query to get all the required nodes to show JAMES node.

with emp_tree as (
 select ename,
        empno,
        mgr,
        sys_connect_by_path(empno, ':')||':' as emp_path
   from emp
  start with mgr is null
connect by prior empno = mgr
  order siblings by ename ),
emp_filtered_data as (
 select ename,
        empno,
        mgr
   from emp
  where exists (select *
                     from (
                         select emp_tree.emp_path
                           from emp_tree 
                          -- searching for employees with name like JAMES
                          where upper(emp_tree.ename) like '%JAMES%') ep
                    where ep.emp_path like '%:'||emp.empno||':%')
)
select *
  from emp_filtered_data

Query output:

ENAME	EMPNO	MGR
KING	7839	-
BLAKE	7698	7839
JAMES	7900	7698

Now, we can use this filtered data to generate tree. So, let's build a demo page using this technique. 

Let's create an empty page and create a text field for entering search term. In demo application, I have used P40_SEARCH as item name. Next, create tree region using below query.

with emp_tree as (
 select ename,
        empno,
        mgr,
        sys_connect_by_path(empno, ':')||':' as emp_path
   from emp
  start with mgr is null
connect by prior empno = mgr
  order siblings by ename ),
emp_filtered_data as (
 select ename,
        empno,
        mgr
   from emp
  where :P40_SEARCH is null
        OR exists (select *
                     from (
                         select emp_tree.emp_path
                           from emp_tree 
                          where upper(emp_tree.ename) like '%'||upper(:P40_SEARCH)||'%') ep
                    where ep.emp_path like '%:'||emp.empno||':%')
)
/*
  If we derive status, level in emp_tree query and if we try to use it here, tree region (sometimes) will throw error
  because filering can change the order of rows and tree region expects correct order (depth first traversal) of rows as generated by hierarchical query
*/
 select case when connect_by_isleaf = 1 then 0 when level = 1 then 1 else -1 end as status,
        level,
        ename as title,
        'icon-tree-folder' as icon,
        empno as value,
        ename as tooltip,
        null  as link
   from emp_filtered_data
  start with mgr is null
connect by prior empno = mgr
  order siblings by ename

Also, please specify

• P40_SEARCH as Page Items to Submit
• emp_tree as Static ID for the tree region. We will use this later in dynamic actions

Now, let's create a dynamic action, which will listen to "Enter Key Press" event of the search field as below.

• Name: Search (or any proper name)
• When:
• Event: Key Down
• Selection Type: Item(s)
•  Item(s): P40_SEARCH
• Client-side Condition:
• Type: JavaScript expression
• JavaScript Expression: this.browserEvent.which === 13
• True Action-1: (to refresh tree region)    
• Action: Refresh
• Affected Elements:
• Selection Type: Region
• Region: <Tree Region>

That's it. Now, if we type anything in search field and hit enter key, tree will show filtered nodes as per the search term. However, it may not be clear to users which nodes are matching the search criteria. Because, we are showing matching nodes and their parent nodes till the root node. So, it would be helpful, if we can highlight the nodes that are matching the search criteria. We can do this using find method of treeView widget.

To implement this, let's keep below JavaScript code in Page > JavaScript > Function and Global Variable Declaration section (preferably in your application specific JavaScript file).

function treeFind ( pTree$, pSearchString, pClassName ) {
    // find the string in tree nodes
    var lMatchedNodes$ = pTree$.treeView( "find", {
        depth: -1,
        findAll: true,
        match: function( n ) {
            return n.label.toLowerCase().indexOf( pSearchString.toLowerCase() ) >= 0;
        }
    } );
    // add CSS class to matching nodes
    lMatchedNodes$.addClass( pClassName );

    // find is used in conjunction with server side filtering
    // so when there are matching nodes, we just expand all nodes, so users can see matched nodes easily
    if ( lMatchedNodes$.length > 0 ) {
        pTree$.treeView( "expandAll" );
    }
}
    
function treeSearch  ( pSearchString, pStaticId, pClassName ) {
    // remove CSS classes from previous search, if any
    apex.jQuery( "." + apex.util.escapeCSS( pClassName ), "#" + apex.util.escapeCSS( pStaticId )).removeClass( pClassName );
    if ( pSearchString !== "" ) {
        treeFind( apex.region( pStaticId ).widget(), pSearchString, pClassName );
    }
}

Next, let's create DA like below.

• Name: Highlight Matching Nodes (or any proper name)
• When:
• Event: After Refresh
• Selection Type: Region
• Region: <Tree Region>
• True Action-1:
• Action: Execute JavaScript Code
• Settings:
• Code: treeSearch ( $v("P40_SEARCH"),'emp_tree','u-color-22-bg' );

In above DA code emp_tree is Static ID specified for the tree region. u-color-22-bg is universal theme utility class for adding background color to matching tree nodes. 

That's it and we are good to go. Here is the demo link.

Thank You 🙏