Overblog Folge diesem Blog
Edit post Administration Create my blog

Building a treetable-like form

10. April 2009 , Geschrieben von Andreas Weiden

The task

I made myself familiar with ADF two years ago. Doing so i also had a look at the different components provided by the ADF-faces-UI. I found one component named TreeTable very nice and useful. A TreeTable component shows hierarchical data in a tabular way, where the first column of the table "contains" the hierarchy and can be used to expand, collapse and drill into the data. The big difference against a simple tree is that the hierarchical part of the data is "synchronized" with the tabular data. So, when scrolling through the data, the tree-part moves along with the table-part.

So, as an "old" forms-developer, i tried to create a similar functionality in forms.

An example

The HR-schema is a good example, for the EMPLOYEES-table provided with the schema contains a hierarchical structure. Each employee has assigned a manager, which in turn also is an employee again. The "top-managers", which are the topmost-level in the hierarchy do not have a manager assigned. So as an example we will build a form which shows all the employees in a tabular view with the possibility to expand and collapse each row so that the "detail"-employees of an employee are shown/hidden in the same block.

The approach

The hierarchical query is not a problem to implement, but how deal with the dynamic loading and hiding of details to an already queried record. I played a while trying to create missing records "by hand" and integrate them into the already queried data in the block, but if found it to be to complicated  (at least, when the block should be updateable). So i tried a different approach:
Whenever a record should be expanded or collapsed, the whole block gets requeried and the cursor gets navigated to the previously select record.
The query has to be adjusted every time the block is requeried so that it contains only the data which should be shown in the block. With each requery, there is either one master expanded, which in turn leads to all detail-employees have to be included in the query, or one master is collapsed, which leads to all detail-employees to be excluded from the query.

To do so, i decided to use an table-type-object containing all id's of the "expanded" employees, and this table-type-object then can be included in the query. To store the instance of the object i use a database-package.

Last part is to include or exclude a master-id to this object from the form.

The implementation

First part is to create an object-type to hold the id's:

CREATE OR REPLACE TYPE T_IDLIST AS TABLE OF NUMBER;

Second part is to create a package to "store" an instance of this object-type, which also includes methods to add an id to the type and to remove it again.

CREATE OR REPLACE PACKAGE PK_TREETABLE IS

  -- Method to reset the list
  PROCEDURE PR_RESET;

  -- Method to retrieve the object
  FUNCTION FK_GET_EXPANDED
  RETURN T_IDLIST;

  -- Method to toggle the given id
  -- If the id is not in the list, include it, else exclude it
  PROCEDURE PR_TOGGLE(i_nId IN NUMBER);

END;

And the body

CREATE OR REPLACE PACKAGE BODY PK_TREETABLE IS

  oIds T_IDLIST:=T_IDLIST(-1);

  PROCEDURE PR_RESET IS
  BEGIN
    -- re-Instantiate the type, given only -1 for the root
    oIds:=T_IDLIST(-1);
  END;

  FUNCTION FK_GET_EXPANDED
  RETURN T_IDLIST IS
  BEGIN
    -- return the internal instance
    RETURN oIds;
  END;

  PROCEDURE PR_TOGGLE(i_nId IN NUMBER) IS
    bFound BOOLEAN:=FALSE;
    iPos PLS_INTEGER;
  BEGIN
    -- check if, the given id is part of the list
      iPos:=oIds.FIRST;
    LOOP
        EXIT WHEN iPos IS NULL;
      IF oIds(iPos)=i_nId THEN
        -- found, delete it
        oIds.DELETE(iPos);
        -- and remember
        bFound:=TRUE;
      END IF;
      iPos:=oIds.NEXT(iPos);
    END LOOP;
    -- If not found
    IF NOT bFound THEN
      -- add it to the list
        oIds.EXTEND(1);
        oIds(oids.COUNT):=i_nId;
    END IF;
  END;
END;

Now we need the view to base our forms-block on

CREATE OR REPLACE VIEW V_EMP
(
  STATUS,
  MASTER,
  EMPLOYEE_ID,
  LAST_NAME,
  FIRST_NAME,
  EMAIL,
  HIRE_DATE
) AS
SELECT CAST('?' AS VARCHAR2(200)) STATUS,
       NVL(MANAGER_ID,-1) MASTER,
       EMPLOYEE_ID,
       LAST_NAME,
       FIRST_NAME,
       EMAIL,
       HIRE_DATE
  FROM EMPLOYEES
 WHERE NVL(MANAGER_ID,-1) IN (SELECT COLUMN_VALUE
                                FROM TABLE(PK_TREETABLE.FK_GET_EXPANDED)
                             )
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID=MANAGER_ID
ORDER SIBLINGS BY LAST_NAME

Now, we create a simple tabular form based on that view and implement a WHEN-MOUSE-DOUBLECLICK-trigger which does the toggling of the id in the package and the requery-part:

PK_TREETABLE.PR_TOGGLE(:V_EMP.EMPLOYEE_ID);
EXECUTE_QUERY;

Showing some treelike status

For now, there is a status-field showing just a question-mark. Lets make it a little nicer by showing a + when a node is collapsed and a minus when it is expanded. We have to adjust the view for that:

CREATE OR REPLACE VIEW V_EMP
(
  STATUS,
  MASTER,
  EMPLOYEE_ID,
  LAST_NAME,
  FIRST_NAME,
  EMAIL,
  HIRE_DATE
) AS
SELECT RPAD(' ', LEVEL-1, ' ') ||
       CASE WHEN SELECTED_EMP IS NULL THEN
         '+'
       ELSE
         '-'
       END STATUS,
       NVL(MANAGER_ID,-1) MASTER,
       EMPLOYEE_ID,
       LAST_NAME,
       FIRST_NAME,
       EMAIL,
       HIRE_DATE
  FROM EMPLOYEES,
       (SELECT COLUMN_VALUE SELECTED_EMP
          FROM TABLE(PK_TREETABLE.FK_GET_EXPANDED)
       )
 WHERE NVL(MANAGER_ID,-1) IN (SELECT COLUMN_VALUE
                                FROM TABLE(PK_TREETABLE.FK_GET_EXPANDED)
                             )
   AND EMPLOYEE_ID=SELECTED_EMP(+)
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID=MANAGER_ID
ORDER SIBLINGS BY LAST_NAME

The missing parts

The GUI for the tree is not satisfying by now, we have to deal with some additional requirements:

  • Show the expand and collapse nodes in a graphical way

  • Show tree-lines

  • Show icons for each node


I'm curently working on building a java-bean for this, i'll show the results in my next post.

Diesen Post teilen

Repost 0

Kommentiere diesen Post

Ozren Pestic 11/02/2010 22:04


Hi,

I work a lot with trees and I found your blog interesting.
I wonder if you were working on this problem:

There is a big tree(30000 terms). I want to make a possibility to search for some term and if the term is found tree should be expanded to that term.

If you have some piece of code it would be appreciated.
Than you.

Ozren


Sim 04/20/2009 18:17

Hi,
Congratulations for your new blog.. It seems really very helpful...
Keep on..!!!!

Note: Sorry for any wrong text i've written in this form ... because i do not understand Dutch....

Thank you,
Sim

Andreas Weiden 04/24/2009 23:22


Thank you Sim.

btw..  I also don't understand Dutch i'm from Germany.

Andreas