Nested Set - Sorting/Ordering by a differnt field.

mythix · #1 (**permalink**) 09-29-09, 06:46

I was wondering if there was an easy way to order a nested set by a column other than `left` or `right` whist still maintaining the hierachical order? Obviously it would be possible to do this after retrieval - but because of the potential nunber of rows this may be too intensive.

For example given the tree:

A (1,8)
- D (2,3)
- C (4,7)
- - B (5,6)

I would want to retrive the result in order:

A (1,8)
- C (4,7)
- - B (5,6)
- D (2,3)

I guess storing these in order is a potential solution - however there are several fields on which ordering should be possible.

Any thoughts?

thanks

gvee · #2 (**permalink**) 09-30-09, 07:02

Can you provide the CREATE TABLE statements, some sample data (in the form of INSERT statements) and the expected results from that sample data please?

mythix · #3 (**permalink**) 09-30-09, 09:09

No problem

Code:

CREATE TABLE  `wats_svn_40`.`jos_whelpdesk_tree` (
  `grp` varchar(100) NOT NULL default '',
  `type` varchar(100) NOT NULL default '',
  `identifier` varchar(100) NOT NULL default '0',
  `parent_type` varchar(100) default NULL,
  `parent_identifier` varchar(100) default NULL,
  `lft` int(11) default NULL,
  `rgt` int(11) default NULL,
  `description` varchar(255) default NULL,
  PRIMARY KEY  (`grp`,`type`,`identifier`),
  KEY `PARENT` (`parent_type`,`parent_identifier`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I should point out that this table is designed to hold more than one tree (separated by the grp field). This table is a sort of index for rows held in other tables - the type field identifies the type of node we are talking about.

In terms of alphabetical order I would only ever be extracting a branch that is made up entgirely of the same type of node.

The table also include a refernce directly to the parent node.

Attached is some sample data (was too big to include easily in the message body text).

To be honest i have been looking around teh internet and am thinking that this is no easy task without post processing.

Internet Services

Web Development

Digital Marketing

Consumer Tech