DBTreeView - hierarchical data and second relation

Questions and answers on developing, deploying and using plugins and JavaBeans

DBTreeView - hierarchical data and second relation

Postby steve1376656734 » Sun Sep 01, 2013 11:15 pm

Hi,

I have a db table (category) with the following structure:

category_id (primary key)
description
parent_id

I have added a relation category$parent_to_child and I can use the following code to create a hierarchical tree using the DBTreeView bean no problem and everything works as expected:
Code: Select all
   var _first_binding = elements.category_tree.createBinding(foundset.getDataSource());

   /*
    * Set the calculation that returns the icon name, the sort order for the nodes, the relation for the child nodes
    * and the field to be used for the node text
    */
   _first_binding.setImageURLDataprovider('display_icon');
   _first_binding.setChildSortDataprovider('tree_sort');
   _first_binding.setNRelationName('category$parent_to_child');
   _first_binding.setTextDataprovider('description')

Now I also have a second table (product) with the structure;

product_id (primary key)
category id
item_name

I want to add this into the DBTreeView and show each product in the tree under its relevant category. I have added the relation category_to_product using the category_id and want to add this to the tree but nothing I try seems to work. I have tried using:
Code: Select all
_first_binding.setMRelationName('category_to_product');

and also
Code: Select all
   var _second_binding = elements.category_tree.createBinding(forms.categories.controller.getDataSource());

   _second_binding.setNRelationName('category_to_product');
   _second_binding.setTextDataprovider('item_name')

but neither give the result I want.

Can anyone give me some pointers on how to achieve this please?

Thanks

Steve
Steve
SAN Developer
There are 10 types of people in the world - those that understand binary and those that don't
steve1376656734
 
Posts: 327
Joined: Fri Aug 16, 2013 2:38 pm
Location: Ashford, UK

Re: DBTreeView - hierarchical data and second relation

Postby jtonyhdz » Mon Sep 02, 2013 10:13 am

Hi Steve

What exactly do you want to do? Is something like this:
-Category 1
Product 1
Product 2
+Category 2
+Category 3

Or

-Category 1
+Sub Category 1
-Sub Category 2
Product 1
Product 2
+Category 2
+Category 3

Or something else?

Please put an example to help you better.

Regards
jtonyhdz
 
Posts: 8
Joined: Thu Nov 15, 2012 4:02 am
Location: Nicaragua

Re: DBTreeView - hierarchical data and second relation

Postby steve1376656734 » Mon Sep 02, 2013 4:27 pm

Hi

I am trying to get the following to be output:

    -Category1
      -SubCategory1
        *Product1
        *Product2
      -SubCategory2
        -SubSubCategory1
        *Product3
    -Category2
      -SubCategory3
        *Product4
      *Product5
So Products could have a category_id that belongs to a root category, a sub category or a sub sub category etc. My tree currently displays the Root categories, sub categories, sub sub categories etc. but I cannot get the Products to display in there anywhere. The two scenarions I described above gave either just the Root nodes being displayed or the Categories being displayed hierarchically (as it does without trying to add the products in).

The data for the above example would be:

Code: Select all
Categories:
category_id    description           parent_category_id
1              Category1             null
2              Category2             null
3              SubCategory1          1
4              SubCategory2          1
5              SubSubCategory1       4
6              SubCategory3          2

Products
product_id     item_name            category_id
1              Product1             3
2              Product2             3
3              Product3             5
4              Product4             6
5              Product5             2

I'm not bothered if the products for a category are listed before or after the sub-categories as long as they are on the correct level.

Hope this explains it and thanks for your help.
Steve
Steve
SAN Developer
There are 10 types of people in the world - those that understand binary and those that don't
steve1376656734
 
Posts: 327
Joined: Fri Aug 16, 2013 2:38 pm
Location: Ashford, UK

Re: DBTreeView - hierarchical data and second relation

Postby jtonyhdz » Mon Sep 02, 2013 10:21 pm

Steve

An option that i found is creating in your database a SQL view to create a full union between the category table and your product table:
Here is the possible definition in your case:

PostgreSQL:
CREATE OR REPLACE VIEW categories AS
SELECT category.category_id * (-1) AS category_id, category.description,
category.parent_id * (-1) AS parent_id
FROM category
UNION ALL
SELECT product.product_id AS category_id, product.item_name AS description,
product.category_id * (-1) AS parent_id
FROM product
ORDER BY 1;

Once you have created the view (let´s say we call it 'myviewcategories'), now you can use it as your data source on the DBTreeView but remember to modify the relation 'category$parent_to_child' so now is pointing to myviewcategories on both side of the relation.

Now with the first binding you already have must works perfect.

Hope this solve your request.

Best regards

Tony
jtonyhdz
 
Posts: 8
Joined: Thu Nov 15, 2012 4:02 am
Location: Nicaragua

Re: DBTreeView - hierarchical data and second relation

Postby steve1376656734 » Fri Sep 06, 2013 2:53 pm

Hi Tony,

I have used your suggestion and it works really well thanks. I had a bit of an issue trying to get checkboxes on the nodes as it was trying to write the values back to the view which wasn't possible. To get around this I added some trigger rules to the database to ignore the updates and this now seems to be doing exactly what I want.

I had also been doing some further research and came across an interesting item in the Wiki for V7.2 regarding the createRelationInfo method of the DBTreeView which seems to imply that I can use this to achieve what I want as it mentions multiple child relations for a tree node:

http://wiki.servoy.com/display/public/DOCS/DB+Tree+View#DBTreeView-createRelationInfo

I am going to put a sample together later and see if it works - I'll post the result here.

Thanks for your help with this.

Steve
Steve
SAN Developer
There are 10 types of people in the world - those that understand binary and those that don't
steve1376656734
 
Posts: 327
Joined: Fri Aug 16, 2013 2:38 pm
Location: Ashford, UK

Re: DBTreeView - hierarchical data and second relation

Postby steve1376656734 » Tue Sep 17, 2013 3:29 pm

OK - I have had a chance to have a brief play with the DBTreeView and the RelationInfo to create multiple bindings for a node and although I can see a use for it it doesn't particularly help in my scenario.

The example that is used in the Wiki would be a good case for using this type of binding where you have a node representing a company and each company has two sub-labels (customers and employees) that then expand to show the details for these categories.

Where this falls down for me is that I have one table that has a hierarchical parent-child relationship and a second table that is related to the first (Categories and Products). Using the RelationInfo object to create the relationship forces me to have a label for the sub-categories that then expands into the items and this does not work well for a hierarchical relationship of this type. The other issue is it will also place a sub-category label into every category even if that category has no sub-categories.

The end result is that although the RelationInfo object does allow you to have multiple relations defined at each level it does look good in my particular scenario and aesthetics are a big part of any solution these days.

Thanks

Steve
Steve
SAN Developer
There are 10 types of people in the world - those that understand binary and those that don't
steve1376656734
 
Posts: 327
Joined: Fri Aug 16, 2013 2:38 pm
Location: Ashford, UK


Return to Plugins and Beans

Who is online

Users browsing this forum: No registered users and 9 guests

cron