Search by child attributes on a tree table

This example illustrates search functionality on a tree table (based  on both master and child record attributes). Steve discusses similar topic in one of his blog post http://blogs.oracle.com/smuenchadf/examples/ Example #150. This post is also based on the same idea (slightly differ, from implementation perspective).

Let me detail the use case with classic Department-Employee example. Your tree table may looks like as shown below. Search mechanism should enable user to search based on attributes both from parent and child records, i.e. Department and Employee entities.

-DeptID , DeptName
|
|__EmpId1, Firstname1, Email1
|__EmpId2, Firstname2, Email2

Obviously implementation part involves two ViewObjects, one for Department and the other for Employee. Next step is to define a ViewCriteriathat act as model for the query. Below image shows a ViewCriteria defined on DeprtmentViewObject.




Define another ViewCriteria on EmployeeViewOject, to filter out the 'Employee' child records programmatically. Please note that, this is used from overridden DepartmentViewObjectImpl::createViewLinkAccessorRS() method as detailed below.



To enable search across child rows(Employee records), I overrode the ViewObjectImpl::createViewLinkAccessorRS(---) as given below(in the DeprtmentViewObjectImpl). This method contains the filtering logic for child rows(Employees).

 @Override
 protected ViewRowSetImpl createViewLinkAccessorRS(AssociationDefImpl associationDefImpl,
                                                     ViewObjectImpl viewObjectImpl,
                                                      Row row,
                                                      Object[] object) {
   ViewRowSetImpl viewRowSetImpl =
       super.createViewLinkAccessorRS(associationDefImpl,
       viewObjectImpl,row, object);
     
   String firstName = getbndVarFirstName();
   if (firstName != null && firstName.trim().length() > 0) {
      ViewCriteriaManager vcm = viewObjectImpl.getViewCriteriaManager();
      ViewCriteria vc = vcm.getViewCriteria("EmployeesViewCriteria");
      VariableValueManager vvm = vc.ensureVariableManager();
      vvm.setVariableValue("bndVarFirstName", getbndVarFirstName());
      viewObjectImpl.applyViewCriteria(vc);
      } else {

         viewObjectImpl.removeApplyViewCriteriaName("EmployeesViewCriteria");
      }

      return viewRowSetImpl;
 }


Rest of the steps remains same as for a normal tree table creation.

You can download the sample workspace from here. This example illustrates the above said use case -Search by child attributes on a tree table.
Run the test.jspx from the attached project. Search panel displays two fields, DepartmentName and FirstName. DepartmentName is used to search the parent record of the tree table, and FirstName filters out the child records.
[Runs with Oracle JDeveloper 11g R1 PS1 + HR Schema]

Comments

  1. Hi,

    The post is really helpful.

    I tried replicating the scenario
    with my own data model.

    What I notice is that my
    createViewLinkAccessorRS in parent VO doesnt get invoked at all.

    I have associated my parentVO and childVO using a viewLink
    and both my parent and childVo read from a view.

    Is there something I am missing here.

    Any help would be highly appreciated.

    Thanks and Regards
    Harish

    ReplyDelete
  2. Hi Harish
    Sounds like all the required settings are in place. Would be great, if you can send me a test case to debug further jobinesh@gmail.com

    Thanks

    ReplyDelete
  3. Hi Jobinesh,

    I got it working today. I had not overriden the bindParametersForCollection in my viewObjectImpl.

    I had another question. I have a TreeTable which is three level deep.

    I was wondering if I can have nested search.

    My scenario involves search for a name(attribute is common to all three levels) across three levels.

    I tried the same approach by overidding the 2 level VO object.

    Somehow the criteria available as part of Level 3 VO object doesnt append in the query.

    Could you let me know if I need to do something else?

    Regards
    Harish

    ReplyDelete
  4. Hi Harish
    You can try out something similar as shown below...
    @Override
    protected ViewRowSetImpl createViewLinkAccessorRS(AssociationDefImpl assocDef,
    ViewObjectImpl accessorVO,
    Row masterRow,
    Object[] values) {
    ViewRowSetImpl v = super.createViewLinkAccessorRS(assocDef, accessorVO, masterRow,
    values);
    ViewCriteria vc = (ViewCriteria)((ViewRowImpl)masterRow).getChildRS().getViewObject().getSomeViewCriteria();
    //make necessary changes and apply it
    accessorVO.applyViewCriteria(vc);
    return v;
    }

    ReplyDelete
  5. Hi Jobinesh,

    I had a couple of questions.

    a) If I had Grandparent,Parent and Child as the VO. In which implementation VO should I place this code. I would assume in the parentVO Implementation.

    b) I tried getting the RowSet from the the ParentRowImpl. My ParentRowImpl returns a RowIterator for ChildVO.

    I understand from the documentation of RowIterator ,RowSet and ViewObject are the known Subinterfaces.

    So this is what I did in my ParentVOImpl's createViewLinkAccessorRS method and I run into a Stackoutflow error which crashes my server. The log for it is below.

    Am i missing here something? Would be great,if you can attach a sample of how this functionality can be achieved.



    So here is what I tried in my Par
    RowIterator rowSet = (RowIterator) ((ParentRowImpl )masterRow).getVendorVO();
    while (rowSet.hasNext()) {
    ChildVORowIMpl=(ChildVORowIMpl) rowSet.next();
    }


    "[ACTIVE] ExecuteThread: '0' for" id=14 idx=0x40 tid=31736 lastJavaFrame=0xb333be28

    Stack 0: start=0xb331e000, end=0xb3340000, guards=0xb3323000 (ok), forbidden=0xb3321000
    Thread Stack Trace:
    at dumpForceDump+117()@0xb7d65545
    at vmFatalErrorMsgV+84()@0xb7edf7e4
    at vmFatalErrorMsg+31()@0xb7edf80f
    at jniPinObject+247()@0xb7dcf6a7
    at jniGetPrimitiveArrayCriticalInfo+57()@0xb7dba729
    at jniGetPrimitiveArrayCritical+46()@0xb7dba78e
    at jniStringFromUTF82+73()@0xb7dcfd99
    at jniStringFromIString2+52()@0xb7dd02e4
    at jmgmtNewStackTraceElem+156()@0xb7db3edc
    at jniGetStackTraceElement+161()@0xb7dc4dc1
    at Java_java_lang_Throwable_getStackTraceElement+45()@0xb6fd46fd
    -- Java stack --


    Thanks
    Harish

    ReplyDelete
  6. Harish
    Please see the uploaded sample from the post. You may need to add the filtering logic for the grandchild to its immediate parentVO.
    I don't know why you need to iterate through the rows as you posted in the code snippet; any specific reason?.

    ReplyDelete
  7. Hi Jobinesh,

    I added the filtering logic for the grandchild to its immediate parentVO.

    I am linking my ParentVo and ChildVo via a ViewLink.

    My ViewObjectImpl has a handle to childVO ,but it returns me a RowIterator insterad of RowSet.(as you mentioned in the post)

    The reason why I iterator is to get the ViewObject. That way I can get the ViewCriteria Object.

    I know I am definitely missing something here.

    I will post my sample app soon.

    Thanks and Regards
    Harish

    ReplyDelete
  8. Hi Jobinesh,

    My apologies for the delay. I have sent you a sample code to your gmail id.

    Thanks and Regards
    Harish

    ReplyDelete
  9. Hi Fellas,

    Thank you for starting this post as it is the only one that addresses an issue very similar to the one I'm trying to solve.

    I am trying to perform filtering on a tree structure, at each level, which carries a 5 tier hierarchy and would like some advice regarding the approach i should take to accomplish this.

    Thank you
    Kwesi

    ReplyDelete
  10. Hi jobinesh..
    I am new in working with trees.. i have a requirement of adding different icons for parent and child nodes .. can u provide me a sample eg.. that can illustrate the same ,,, it would be very helpful for me...
    my mail id.. vini.gopal@gmail.com

    ReplyDelete
  11. Dear Sir,
    I Am New In ADF,
    If I send you my example can you see what i miss

    ReplyDelete
  12. Some days ago I was looking for a program that helps me with my family tree but I couldn't find anything a friend told me that I must visit this page and now I'm so glad because I could finish my college's project.

    ReplyDelete
  13. I've a three level hierarchy VO's: Master, Child, GrandChild

    I tried to create a VC in MasterVO while associating exists conditions till GrandChildVO through ChildVO. When I run my AM and testing this VC by passing appropriate bind parameter to GrandChildVO condition, it is failing with SQL exception.

    I've observed that framework is reparing SQL incorrectly.

    Could you pls let me know how you overcome this!

    ReplyDelete
  14. Pramod,
    Can you pass me a test case(using HR schema) illustrating the issue.
    thanks
    Jobinesh

    ReplyDelete
  15. Hi Jobinesh,

    Thanks for the quick reply to my comment.

    I've attached a sample based on HR schema sent to your email, and sample application is developed using Jdev 11.1.1.4.

    Also, in the sample uploaded by you wrt this blog post I need an enhancement for my client requirement. I want to search by multiple employee first names on the child (i.e EmployeeView). To achieve this I tried "Add Fields" from af:query, but as ADF creates VC_TEMP bind variables for the "Add Fields" I ran out of ideas as how I could filter the child ((i.e EmployeeView) with the advanced criteria entered from af:query component. Appreciate any hints for me to investigate!

    Thanks,
    Pramod Gujjeti

    ReplyDelete
  16. Jobinesh thanks for the code.
    I was stuck for a day, and now, thanks to your post, my use case works. I am able to filter on leaf nodes on a 2 level treeTable.

    Next I want to filter on the third level of a 4 level hierarchy. I should be able to use the same approach.
    Thanks again
    Nicholas

    ReplyDelete
  17. Hi Jobinesh,

    I am using Jdevloper 11.1.1.4 and can't migrate to 11.1.1.5 (as per client requirement). I need to pass bind variable in childVO. Could you please help me in. I have hierarchy like :
    Parent
    >child
    > child
    >child
    3 level hierarchy and each childVo i need to pass bind variable at run time.

    ReplyDelete
  18. Is this tree table or simple master-child display?

    ReplyDelete
  19. Hi, i have the same problem but my tree is a n level tree, and in design time i can't know how deep are going to be my tree. I make it from one view object, i try to override the createViewLinkAccessorRS() method like the example above but sometimes its get called and sometimes not, and don't work. For example, the hr schema has the employees table, i construct the tree only using the AllEmployeeVO and the tree are going to be very big, how can i search over the hole tree using the af:query and treetable? Sorry for my English...Regards

    ReplyDelete
  20. Hi Jobinesh,

    I have a tree with parentVo-->ChildVO-->GarndChildVo

    Here ChildVotoGrandChildVo has selfJoin.

    I don't know How many levels i have in the tree since it is self join.

    If i give searchinput as 'Parent1_Child1_grandChild1_gc11'

    I have to filter only
    Parent1-->Parent1-->
    Child1-->grandChild1-->gc11

    hierarchy from full tree.

    can you suggest me the way to do it?

    ReplyDelete
  21. Hi jobinesh,

    The Search field using QuickQuery displaying along with a drop down for Attributes.. but I dont want to display the drop down .. is this possible ?

    ReplyDelete
  22. Hi Jobinesh,
    I was trying to implement the same using quickQuery component instead of using af:query. I used the same bindings,value and model attributes and tried. It threw an error
    Caused by: oracle.jbo.NoDefException: JBO-25058: Definition DimensionVVOCriteria.vcrow91.HierarchyVO.HierarchyVONestedCriteria.vcrow93.Name of type Attribute is not found in DimensionVVO.
    at oracle.jbo.server.ViewObjectImpl.findAttributeDef(ViewObjectImpl.java:7631)
    at oracle.adfinternal.view.faces.model.binding.FacesCtrlSearchBinding._isTransientAttribute(FacesCtrlSearchBinding.java:544)
    at oracle.adfinternal.view.faces.model.binding.FacesCtrlSearchBinding._changeQueryModeIfNeeded(FacesCtrlSearchBinding.java:536)
    at oracle.adfinternal.view.faces.model.binding.FacesCtrlSearchBinding.processQuery(FacesCtrlSearchBinding.java:407)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    ... 53 more

    ReplyDelete

Post a Comment

Disclaimer

The views expressed on this blog are my own and do not necessarily reflect the views of my employer.