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

Harish said…
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
Jobinesh said…
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
Harish said…
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
Jobinesh said…
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;
}
Harish said…
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
Jobinesh said…
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?.
Harish said…
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
Harish said…
Hi Jobinesh,

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

Thanks and Regards
Harish
Kwesi said…
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
Vinitha said…
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
AZM said…
Dear Sir,
I Am New In ADF,
If I send you my example can you see what i miss
Viagra Online said…
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.
Pramod Gujjeti said…
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!
Jobinesh said…
Pramod,
Can you pass me a test case(using HR schema) illustrating the issue.
thanks
Jobinesh
Pramod Gujjeti said…
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
Anonymous said…
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
Anonymous said…
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.
Jobinesh said…
Is this tree table or simple master-child display?
EusebioGarcia said…
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
Anonymous said…
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?
swath said…
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 ?
kiran said…
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
Rakesh said…
Hi,

I have a query based VO which has bind variables inside Query(didn't create any Criteria) as Follows
Parent - > Child - > Child-Child VO's.

In Child VO and Child-Child VO also I have bind Variables inside query.
Using code i want to set those bind variables for the Child VO's

Can you help me with this?????
Hi Jobinesh,

I know that this is an old post, but I don't see any reason why is not working in 12.1.3

If anyone has noticed that maybe could help

Thanks a lot!!

Disclaimer

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