Tree structure [message #4323] |
Sat, 05 July 2014 06:17 ![Go to next message Go to next message](/theme/default/images/down.png) |
htManager
Messages: 427 Registered: May 2014
|
Senior Member |
|
|
Hi Tony,
I worked through the tutorials and trough the article 'A Flexible Tree Structure' to implement my own tree structure. It is very interesting to see how you can build such flexible structures.
Now my question: Is it possible to build a tree structure and a tree view with other (existing) tables with cascading primary keys? I would like to show a hirarchy where I can use the occurances of existing tables. If I insert the levels and nodes in the three tables x_tree_type, x_tree_level and x_tree_node I would have to do twice.
|
|
|
|
|
|
Re: Tree structure [message #4647 is a reply to message #4645] |
Thu, 12 February 2015 04:34 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
It would be impossible for me to determine what is wrong with your code just from this description. I could only do that by stepping through the code with my debugger, but that would require you to send me a copy of your, and your database schema, and your test data.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
|
Re: Tree structure [message #4657 is a reply to message #4656] |
Sat, 28 February 2015 08:31 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
htManager
Messages: 427 Registered: May 2014
|
Senior Member |
|
|
I suppose that the queries are generated in the _cm_getNodeData of the class file. I copied the code from the xample file and tried to change it. But I haven't much experience in generating SQL-queries, so that I suppose that I did something wrong. Especially because I have a primary key consisting of several fields. Here is my code, maybe you can see what I am doing wrong:
$this->sql_select = 'hallen_plaetze_nodes.node_id, '
. 'hallen_plaetze_nodes.hallen_plaetze_node_desc, '
. 'hallen_plaetze_nodes.hallen_plaetze_nodes_logo_fname, '
. 'hallen_plaetze_level.hallen_plaetze_level_seq, '
. 'COUNT(child.node_id) AS child_count';
$this->sql_from = 'hallen_plaetze_nodes '
. 'LEFT JOIN hallen_plaetze_level ON '
. '(hallen_plaetze_level.verbaende_art_id=hallen_plaetze_nodes .verbaende_art_id AND '
. 'hallen_plaetze_level.verbaende_kuerzel=hallen_plaetze_nodes .verbaende_kuerzel AND '
. 'hallen_plaetze_level.halle_no=hallen_plaetze_nodes.halle_no AND '
. 'hallen_plaetze_level.hallen_plaetze_level_id=hallen_plaetze _nodes.hallen_plaetze_level_id) '
. 'LEFT JOIN hallen_plaetze_nodes AS child ON (hallen_plaetze_nodes.node_id=child.node_id_snr) ';
$this->sql_where = '';
$this->sql_groupby = 'hallen_plaetze_nodes.node_id, '
. 'hallen_plaetze_nodes.hallen_plaetze_node_desc, '
. 'hallen_plaetze_nodes.hallen_plaetze_level_id, '
. 'hallen_plaetze_level.hallen_plaetze_level_seq';
$this->sql_having = '';
$this->sql_orderby = 'hallen_plaetze_nodes.hallen_plaetze_level_id,'
. 'hallen_plaetze_nodes.node_id';
|
|
|
Re: Tree structure [message #4658 is a reply to message #4657] |
Sun, 01 March 2015 04:23 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
I cannot possibly debug such a complicated SQL query unless I have a copy of your database schema, some test data, and a description of what you are trying to do.
You really should learn more about SQL and writing queries as without this knowledge you will find it difficult to extend the framework. You need to play with your queries outside of the framewor until you find the right query, then get the framewor to generate that query.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
Re: Tree structure [message #4707 is a reply to message #4697] |
Fri, 24 April 2015 04:07 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
You have to adjust the first query to only select those entries at the top of the tree (ie: with tree_level=1). You are controlling the queries which the code issues, so it is up to you to get those queries right.
For your information I have successfully implemented tree views in my enterprise application using tables which do not include column names such as TREE_TYPE, TREE_LEVEL or TREE_NODE, and which have compound primary keys, so I know that it is definitely possible to do so. My most recent usage was to display a Bill of Materials (BOM) in my PRODUCT database which uses a single table which contains the product hierarchy, as shown below:
CREATE TABLE `product_component` (
`product_id_snr` VARCHAR(40) NOT NULL,
`product_id_jnr` VARCHAR(40) NOT NULL',
`seq_no` SMALLINT(5) UNSIGNED NOT NULL,
`revision_id_snr` VARCHAR(16) NULL DEFAULT NULL,
`revision_id_jnr` VARCHAR(16) NULL DEFAULT NULL,
`quantity` DECIMAL(15,6) UNSIGNED NOT NULL DEFAULT '1.000000',
`start_date` DATE NOT NULL DEFAULT '2000-01-01',
`end_date` DATE NULL DEFAULT '9999-12-31',
PRIMARY KEY (`product_id_snr`, `product_id_jnr`, `seq_no`)
);
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
|
|
Re: Tree structure [message #4760 is a reply to message #4759] |
Tue, 02 June 2015 13:05 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
This ADD3 pattern is normally listed as an option with a LIST2 pattern. If you do not want, or cannot create as LIST pattern you can always create twe script mannually. Just copy the ADD3.PHP script from the "default" directory into your subsystem directory, rename as required, amend the rerferences inside it as required, then manually create an entry on the MNU_TASK table.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
Re: Tree structure [message #7559 is a reply to message #7558] |
Mon, 11 January 2021 13:50 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
If you have 3 levels they are numbered 1, 2 and 3. If you want to insert a new level between 2 and 3 you must add it as level 4 first, then you can use the "Update Level Sequence Number" task to swap levels 3 and 4 around. This prevents you from specifying a sequence number that does not currently exist.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
Re: Tree structure [message #7562 is a reply to message #7561] |
Wed, 13 January 2021 05:25 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
My Tree Structure is not a universal design for all possible hierarchical structures, so may not be the most optimal solution in all circumstances. For example, in my ERP system I have a PRODUCT subsystem which needs to include a Bill Of Materials (BOM) which can go down many levels. For this I have a single table which holds the entire hierarchy using just 3 columns - product_id_snr, product_id_jnr and quantity.
You should start by drawing a structure diagram which shows all the levels in your hierarchy and identify which are nodes and which are leaves. Remember that an outside table, such as PLAYER, could include a node_id column as a foreign key which points to one of the entries, at any level, in the tree structure.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
Re: Tree structure [message #7564 is a reply to message #7563] |
Thu, 14 January 2021 05:09 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
It is always a good idea to draw the hierarchy structure on a piece of paper first so that you can draw the lines of connectivity between each element in that structure so that you can see for each node which is its parent and which are its children. You may also need to identify where connectivity is invalid so must be prevented. In my TREE structure each node can have only one parent (or no parent for the top node) but any number of children. Does the same rule apply in your structure.
Once you have drawn a structure which identifies and follows all the rules it will be easier to map that structure to tables in the database, and if the structure is right the coding will be (relatively) easy.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
Re: Tree structure [message #7567 is a reply to message #7566] |
Sun, 24 January 2021 14:57 ![Go to previous message Go to previous message](/theme/default/images/up.png) |
htManager
Messages: 427 Registered: May 2014
|
Senior Member |
|
|
I could create a proper tree structure which is working fine. Now I wanted to create a popup3 pattern with this tree structure. In http://www.tonymarston.net/php-mysql/dialog-types.html#popup you can see that this should be a list1 pattern as popup. But in the creation process I have to choose an outer table just as in the popup4 pattern. And the popup4 *.php pattern looks the same as the *.php popup3 pattern. Is this correct or do I have to do something else?
It was my fault. I chose the wrong type. Everything works fine. Sorry for the post.
[Updated on: Mon, 25 January 2021 03:17] Report message to a moderator
|
|
|