Re: Nested Sets and custom sorting

I'm not sure what your procedure looks like for populating the lft and rgt values, but I would suggest adding an ORDER BY to a SELECT statement somewhere within that code. Then when you ORDER BY lft in your queries, that sorting will be maintained.

You would, of course, need some sort of if statement to insert different column names for the ORDER BY depending on the value of Listorder for each parent.

On 3 Aug 2004 03:09:34 -0700, mpluijmaekers_at_yahoo.com (Mark) wrote:

>Hello everyone,
>
>I'm having a problem with Joe Celko's nested set model for hierarchies
>combined with custom sorting. My ultimate goal is to generate a dhtml
>navigation menu based on a table in a sql server 2000 database. The
>main columns of this table are:
>
>CREATE TABLE [dbo].[Contents] (
> [Nr] [int] IDENTITY (1, 1) NOT NULL ,
> [Parent] [int] NULL ,
> [Hidden] [bit] NOT NULL ,
> [Title] [varchar] (150) NOT NULL ,
> [ListOrder] [varchar] (50) NULL ,
> [ShowFrom] [datetime] NULL ,
> [ShowUntil] [datetime] NULL ,
> [SortIndex] [smallint] NOT NULL ,
> [Folder] [bit] NOT NULL)
>GO
>
>Where
>Nr: id of the item
>Parent: the parent of the item
>Hidden: indicates whether to show the item or not
>Title: the name of the item
>Listorder: a string representing the way of sorting of the children of
>the items, eg ‘alphabetic', ‘defaul', ‘date' or ‘custom'.
>ShowFrom: the date the item should be shown from
>ShowUntil: the date the item should be shown until
>SortIndex: an integer indicating the sortorder in case
>parent.listorder = ‘custom'.
>Folder: indicates whether the item can contain subitems/children.
>
>The table contains about 1000 items at this time and I don't expect it
>to grow beyond 2000. Because the structure of this table is dictated
>by the vendor of the software (a content management solution), I can
>not make any changes to this table. So what I thought I would do is to
>fully re-populate/re-generate another table a couple of times a day
>using the nested sets model. This table would look like:
>
>CREATE TABLE MyMenu (
> Nr INTEGER NOT NULL,
> Title VARCHAR (150) NOT NULL,
> lft INTEGER,
> rgt INTEGER,
> level INTEGER)
>GO
>
>This works pretty nice, and ordering by lft, I can use this to
>generate the dhtml menu, writing a new line of dhtml for every record
>and writing some extra code on every switch to a higher/lower level.
>However, the menu is of course not sorted as indicated by the
>ListOrder and SortIndex fields and the problem is I can't figure out
>how to incorporate this sorting into the nested sets model.
>
>Does anyone have any suggestions? Or is there another ‘pattern' I
>should use for this kind of problem?
>
>Thanks,
>Mark.
Received on Fri Aug 06 2004 - 00:06:30 CEST