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:
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:
_first_binding.setMRelationName('category_to_product');
and also
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
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
Hi
I am trying to get the following to be output:
-Category1
-SubCategory1
*Product1
*Product2
-SubCategory2
-SubSubCategory1
*Product3[/list:u]
-Category2
-SubCategory3
*Product4
*Product5[/list:u]
[/list:u]
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:
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
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
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
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