How to generate id for easily sorting purpose in SQL Server?

Question

Hi everyone,

I'm making an website and I have problem generate the parent/child tree like:

Page 1
---Sub page 1
------Sub page 1 - 1
Page 2
Page 3
---Sub page 3
------Sub page 3 - 1
------Sub page 3 - 2

If I use UID, it's impossible to write the "ORDER BY" t-sql to make the tree. I'm thinking of a function that can generate the ID (varchar) such as:

001000000
---001001000
------001001001
002000000
003000000
---003001000
------003001001
------003001002

I'm still looking for a solution so if you have any exp with this kind of problem, please help me.

Thanks a lot!

Answer 1

I would skip the custom Id and store parent/child relationships in the database. Then use a recursive query to build your tree.

Answer 2

For web-pages, it's straightforward to use the URL for this. Toby's idea is also okay, but it is probably more complicated than you need (although I am actually doing what he's telling you to, in another application, so I'm not dissing it, in right circumstances). Your scheme would actually work, too.

However, why would you want a function to do it for you? In a flat scheme of things, you can be happy to let AUTO_INCREMENT do your work for you; but in a hierarchy you want to decide where things go, and no function can do it for you.

Answer 3

Maybe you can store the parent id in the table and instead of trying to do this in the table, simply return the rows and use recursion to build you're tree as follows, for optimization after the first run, you could store the tab order and the sort order in a property you assign on the first recursive run in you're data class. Here is an example...

class Program
{


    static void Main(string[] args)
    {
        Program program = new Program();

        List<Data> rows = new List<Data>();
        program.CreateData(rows);

        Console.WriteLine("Data ...");
        program.ShowData(rows);
        Console.WriteLine("");

        Console.WriteLine("Data as tree ...");
        program.ShowDataAsTree(rows);
        Console.WriteLine("");

    }


    public void ShowData(List<Data> rows)
    {
        foreach (Data row in rows)
        {
            Data parent = rows.Find(item => item.Id == row.Parent);
            Console.WriteLine(String.Format("Name = {0}, Parents Name = {1}", row.Text, parent == null ? "" : parent.Text));
        }
    }

    public void ShowDataAsTree(List<Data> rows)
    {
        rows.Sort((item1, item2) => item1.Text.CompareTo(item2.Text));
        ShowDataSortedWrk(rows, Guid.Empty, 0);
    }

    public void ShowDataSortedWrk(List<Data> rows, Guid parentId, int tab)
    {
        foreach (Data row in rows)
        {
            if (row.Parent == parentId)
            {
                for (int i = 0; i < tab; i++)
                {
                    Console.Write("\t");
                }

                Console.WriteLine(row.Text);

                ShowDataSortedWrk(rows, row.Id, tab + 1);
            }
        }
    }

    public void CreateData(List<Data> rows)
    {
        Data alice = new Data(Guid.Empty, "Alice");
        rows.Add(alice);

        Data eric = new Data(Guid.Empty, "Eric");
        rows.Add(eric);

        Data mike = new Data(alice.Id, "Mike");
        rows.Add(mike);

        rows.Add(new Data(mike.Id, "Mark"));
        rows.Add(new Data(eric.Id, "Jane"));
        rows.Add(new Data(alice.Id, "Emma"));
        rows.Add(new Data(mike.Id, "Fred"));
        rows.Add(new Data(alice.Id, "Perry"));
        rows.Add(new Data(eric.Id, "Julie"));
        rows.Add(new Data(eric.Id, "Paul"));
    }
}

public class Data
{
    public Data(Guid parent, string text)
    {
        this.Id = Guid.NewGuid();
        this.Parent = parent;
        this.Text = text;
    }

    public Guid Id
    {
        get;
        set;
    }


    public Guid Parent
    {
        get;
        set;
    }


    public String Text
    {
        get;
        set;
    }
}

Answer 4

Look into the nested set model for hierarchies. Joe Celko has a book which covers this in addition to other ways to model trees and hierarchies in SQL. With the nested set model you would have something like this:

CREATE TABLE Tree_Nodes
(
    lft     INT         NOT NULL,
    rgt     INT         NOT NULL,
    name    VARCHAR(40) NOT NULL,
    CONSTRAINT PK_Tree_Nodes PRIMARY KEY CLUSTERED (lft, rgt)
)
INSERT INTO Tree_Nodes (lft, rgt, name)
SELECT 1, 6, 'Page 1' UNION ALL
SELECT 2, 5, 'Sub page 1' UNION ALL
SELECT 3, 4, 'Sub page 1 - 1' UNION ALL
SELECT 7, 8, 'Page 2' UNION ALL
SELECT 9, 16, 'Page 3' UNION ALL
SELECT 10, 15, 'Sub page 3' UNION ALL
SELECT 11, 12, 'Sub page 3 - 1' UNION ALL
SELECT 13, 14, 'Sub page 3 - 2'

Then to get the result that you're trying to get, it's simply:

SELECT
    lft,
    rgt,
    name
FROM
    Tree_Nodes
ORDER BY
    lft