Skip to main content

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.

Oracle APEX Tree Search

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:

ENAMEEMPNOMGR
KING7839-
BLAKE76987839
JAMES79007698

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 🙏

Comments

Jaydip Bosamiya said…
Thanks Sri, this saves my time a lot.
عبد الله said…
Thank you sir for your valuable information, but I had a problem with Highlight Matching Nodes it is appear for part of second and disappear immediately
Hari said…
Hi, Would it be possible to set-up an example at apex.oracle.com and send workspace/developer-username/password details?
Harry said…
Should this work if you filter by parent? This works for me if I filter on a child but if I filter on the parent the children will only show up if they match the search pattern.

Org Name
Anchorage
-> District 1
New Orleans
-> New Orleans 1
-> New Orleans 2

If I search on District it shows Anchorage and District 1. If I search on Anchorage it only shows Anchorage, no child. If I search New Orleans its shows parent and child.

If I search on parent I expect to see children even if they don't match.
Hari said…
Hi Harry,

Behavior you are explaining is expected. Code explained in this blog searches for matching content by node text. When any node matches the search criteria, it also shows the parent nodes that are required to show the matched node. When parent node matches, then it will only show the parent node. It will show the child nodes, only if they match the search criteria.

Regards,
Hari
Hi,

thanks for sharing this info. In my case after follow all steps I had a problem with Highlight Matching Nodes it is appear for a moment (expand the tree and highlite the result in orange color) and then disappear immediately (collapse the tree and no color).

Any idea why ? have I miss any step ?

thanks
Javier
Hari said…
Hi Javier,

Would it be possible to set-up an example at apex.oracle.com and send workspace/developer-username/password details via contact form? or just comment here? Thank you.
Marion said…
Thank you Hari -
My tree is on page 0, so I don't have the option of putting the JavaScript code in Page > JavaScript > Function and Global Variable Declaration.

Is there somewhere else I can put it?
Hari said…
Hi Marion,

You can put JavaScript code in a file and then you can refer the file in application. Please refer "Adding JavaScript to an APEX application" section in JavaScript documentation.

Popular posts from this blog

Interactive Grid - Conditional Enable/Disable

In this blogpost, I am going to discuss few approaches using which we can conditionally enable/disable Interactive Grid (IG) column(s) based on other column(s) values. Note:    There is a bug  30801170  in APEX 19.2/20.1 with respect to "enable/disable" dynamic actions for IG columns. Workaround for this bug is provided at the end of this blogpost . This bug has been fixed in APEX version 20.2. Client Side Only Conditions If conditions to enable/disable are simple, then we can check those conditions easily on the client side. For e.g. let's consider IG on EMP table with following SQL Query. SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP Let's consider the requirement as - Enable "Commission" column only when JOB is equals to 'SALESMAN' and disable "Commission" column in all other cases. This can be done declaratively using dynamic actions (DA) DA "Enable/Disable Commission - 1" Create DA and give it a prope

Interactive Grid - Bulk Operation on Selected Rows

What's the Problem? Let's say you have an IG on employee table and you want to update selected employees commission based on user's input. Ideally, it should be very simple, where you can write UPDATE statement in page process and select IG region as "Editable Region" under "Execution Options" of the page process. But, when you select rows and submit page, you can see that, this process won't get executed! The reason is  Selection of 'Row Selector' check-boxes is not considered as row-change. Thus selected rows are not submitted to server. So, is there any work around?  Yes! Luckily there are lot of JavaScript (JS) APIs available to work with IG. If you are not already aware, you can refer "APEX IG Cookbook"  or  JavaScript API Reference documentation. If we continue with above Employee IG example, when user selects IG rows, enters "Commission %" and clicks on "Update Commission" button, then we can writ

Interactive Grid - Process Filtered Data on Server Side

Recently one of the APEX developers has reached out to me and asked if it's possible to capture filtered rows data of the Interactive Grid on the server-side and do some processing. In APEX 20.1, there is a new API APEX_IG , using which we can achieve this. Photo by Jakub Kapusnak on Unsplash The approach is very simple and straightforward. Get the internal region id based on the Static ID given for the IG region Get the last viewed report id based on region id Open query context for the region and report using region id and report id Fetch and loop through the rows using the query context Do something with fetched rows And finally, close the query context If you have already done this for interactive reports, then you should be already aware of these steps. The only difference here is, we use APEX_IG APIs instead of APEX_IR APIs. For the demo purpose, let's Build an Interactive Grid on EMP table and let's give it a Static ID as emp Create a Textarea page item with