Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2002

    Question Unanswered: Nested Set - Sorting/Ordering by a differnt field.

    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?


  2. #2
    Join Date
    Jan 2007
    Provided Answers: 12
    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?
    Home | Blog

  3. #3
    Join Date
    Jun 2002
    No problem

    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`)
    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.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts