Results 1 to 9 of 9

Thread: [RESOLVED] Walking a tree with HeirarchyID - SQLServer 2012

  1. #1

    Thread Starter
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Resolved [RESOLVED] Walking a tree with HeirarchyID - SQLServer 2012

    I have a table that contains a field that is type HeirarchyID.
    In fact the table looks like this:
    ID - uiniqueidentifier
    Name - Name of the record
    Description - Description
    RecID - user entered record number
    HeirarchyPath - the Heirarchy ID for the data

    So the table represents a hierarchical view of the structure of a location.

    Code:
    -- Location
    -- -- Sub Location 1.1
    -- -- -- sub sub location 1.1.1
    -- -- -- -- And another location 1.1.1.1
    -- -- -- -- And another location 1.1.1.2
    -- -- -- sub sub location 1.1.2
    -- -- -- -- And another location 1.1.2.1
    -- -- -- -- And another location 1.1.2.2
    -- -- -- -- And another location 1.1.2.3
    -- -- -- -- And another location 1.1.2.4
    -- -- -- sub sub location 1.1.3
    -- -- -- -- And another location 1.1.3.1
    -- -- -- -- And another location 1.1.3.2
    -- -- -- -- And another location 1.1.3.3
    -- -- Sub Location 1.2
    -- -- -- sub sub location 1.2.1
    -- -- -- -- And another location 1.2.1.1
    -- -- -- -- And another location 1.2.1.2
    -- -- -- sub sub location 1.2.2
    -- -- -- -- And another location 1.2.2.1
    -- -- -- -- And another location 1.2.2.2
    -- -- -- -- And another location 1.2.2.3
    -- -- -- -- And another location 1.2.2.4
    -- -- -- sub sub location 1.2.3
    -- -- -- -- And another location 1.2.3.1
    -- -- -- -- And another location 1.2.3.2
    -- -- -- -- And another location 1.2.3.3
    -- -- Sub Location 1.3
    -- -- -- sub sub location 1.3.1
    -- -- -- -- And another location 1.3.1.1
    -- -- -- -- And another location 1.3.1.2
    -- -- -- sub sub location 1.3.2
    -- -- -- -- And another location 1.3.2.1
    -- -- -- -- And another location 1.3.2.2
    -- -- -- -- And another location 1.3.2.3
    -- -- -- -- And another location 1.3.2.4
    -- -- -- sub sub location 1.3.3
    -- -- -- -- And another location 1.3.3.1
    -- -- -- -- And another location 1.3.3.2
    -- -- -- -- And another location 1.3.3.3
    -- -- Sub Location 1.4
    -- -- -- sub sub location 1.4.1
    -- -- -- -- And another location 1.4.1.1
    -- -- -- -- And another location 1.4.1.2
    -- -- -- sub sub location 1.4.2
    -- -- -- -- And another location 1.4.2.1
    -- -- -- -- And another location 1.4.2.2
    -- -- -- -- And another location 1.4.2.3
    -- -- -- -- And another location 1.4.2.4
    -- -- -- sub sub location 1.4.3
    -- -- -- -- And another location 1.4.3.1
    -- -- -- -- And another location 1.4.3.2
    -- -- -- -- And another location 1.4.3.3
    Of course it's not as balanced as all that, but should give you an idea.
    So what I'm trying to do now is display a chunk of that. Displaying the whole tree isn't an issue, got that covered.
    Let's say they user selects Sub Location 1.3 ... I want to be able to see where in the tree it is (getting its ancestors) - this I have covered.
    Where I'm running into trouble is walking the tree DOWNwards... I want to start with Sub Location 1.3 and display its children and their children, and own down.

    I can get the query that starts with the selected node (for lack of a better term) and display its ancestry.
    I can get a query that displays the immediate children of the selected node.

    What I can't get is grandchildren and great-grandchildren.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  2. #2
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: Walking a tree with HeirarchyID - SQLServer 2012

    So, basically what you are asking if you search for 1.3, then you want to get this result:
    -- -- Sub Location 1.3
    -- -- -- sub sub location 1.3.1
    -- -- -- -- And another location 1.3.1.1
    -- -- -- -- And another location 1.3.1.2
    -- -- -- sub sub location 1.3.2
    -- -- -- -- And another location 1.3.2.1
    -- -- -- -- And another location 1.3.2.2
    -- -- -- -- And another location 1.3.2.3
    -- -- -- -- And another location 1.3.2.4
    -- -- -- sub sub location 1.3.3
    -- -- -- -- And another location 1.3.3.1
    -- -- -- -- And another location 1.3.3.2
    -- -- -- -- And another location 1.3.3.3

    ?

  3. #3

    Thread Starter
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Walking a tree with HeirarchyID - SQLServer 2012

    In short - yes. 1.3 doesn't need to be a part of it, since I'll already have it.

    If I can get it into this format:

    ID -- RecId -- Name -- Description -- ParentID (or child ID, I'm not that picky)

    Once I can get it like that, the UI will know how to render it correctly. It's just getting the sql to produce the right IDs so I can link them together.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: Walking a tree with HeirarchyID - SQLServer 2012

    Is the format of HeirarchyPath really like this "1.3.1.2", because then you should be able to get the data by simply doing a search like
    SELECT * FROM your_table WHERE HeirarchyPath LIKE '1.3.%'

  5. #5

    Thread Starter
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Walking a tree with HeirarchyID - SQLServer 2012

    Ah...you've never worked with the HeirarchyID type before have you? Sadly, It doesn't quite work that way.

    The HeirarchyID type is one of those funny types... you set it by giving it a tree value, eg "0/3/1/2" the 0 represents the root, the 3 represents the first child (to the root) with a node value of 3, and on down the line. But SQL Server doesn't store "0/3/1/2" .. because of course that would be too easy (and my my life a whole lot easier) ... instead it stores a hash value of some kind - "0x5C396E" Oh, and because it's a special type, it now has methods and functions...

    Example:

    sql Code:
    1. -- Get the immediate children
    2. select
    3.     LOCATION.ID,
    4.     LOCATION.LOCID,
    5.     LOCATION.NAME,
    6.     LOCATION.DESCRIPTION,
    7.     LOCATION.HIERARCHYPATH,
    8.     (select PARENTLOC.ID from dbo.LOCATION as PARENTLOC where PARENTLOC.HIERARCHYPATH = LOCATION.HIERARCHYPATH.GetAncestor(1)) as PARENTID
    9. from dbo.LOCATION
    10. where (select PARENTLOC.ID from dbo.LOCATION as PARENTLOC where PARENTLOC.HIERARCHYPATH = LOCATION.HIERARCHYPATH.GetAncestor(1)) = @CONTEXTID

    @CONTEXTID is a uniqueidentifier that's passed in (in the example it represents 1.3)

    This is what lets me walk the tree upward:
    LOCATION.HIERARCHYPATH.GetAncestor(1)

    There is a GetDescendents (sp?) method, but it takes a start and end child hID, and returns all nodes along that path... which is close, but I don't know the end child and only really gets a path, it won't include siblings.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Walking a tree with HeirarchyID - SQLServer 2012

    Well, I'll be... your idea did work... Turns out, that if you use .ToString() on a hID field, it unhashes the value and returns the original tree value ... from which I can then use a LIKE and get what I want.
    Code:
    SELECT 
        LOCATION.ID,
        LOCATION.LOCID,
        LOCATION.NAME,
        LOCATION.DESCRIPTION,
        LOCATION.HIERARCHYPATH,
        (SELECT PARENTLOC.ID FROM dbo.LOCATION AS PARENTLOC WHERE PARENTLOC.HIERARCHYPATH = LOCATION.HIERARCHYPATH.GetAncestor(1)) AS PARENTID
    FROM dbo.LOCATION
    where LOCATION.HIERARCHYPATH.ToString() like (select LOCATION.HIERARCHYPATH.ToString() + '%' from LOCATIONwhere LOCATION.ID = @CONTEXTID)
    I'm now getting exactly what I wanted.

    Sometimes all you need is a shove in the right direction.

    Thanks!

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: [RESOLVED] Walking a tree with HeirarchyID - SQLServer 2012

    Hmm... interesting, I am glad I solved your problem

    At work, we are still using SQL Server 2005, we will soon upgrade to 2012, and I can't wait to learn the new features.

    Normally, when I do hierarchy trees, I put in the child the parent ID, this way, I can use CTE recursion (the "with" keyword), and find parent, children, etc...

  8. #8

    Thread Starter
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: [RESOLVED] Walking a tree with HeirarchyID - SQLServer 2012

    Yeah, I used a CTE to walk it upwards, and was trying to do the same going down... which is where I was running into this problem. I had the immediate children... but couldn't figure out a smooth way to say, OK, I have these nodes, find the next set of nodes, where their parentIDs match the IDs of this set... repeat until we hit the bottom of the tree. I either kept getting jsut the immediate set, OR the recursive CTE blew up and trhowing an error (nesting too deeply).

    Either way, I learned something new, and the query works perfectly.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9
    Code Monkey wild_bill's Avatar
    Join Date
    Mar 2005
    Location
    Montana
    Posts
    2,993

    Re: [RESOLVED] Walking a tree with HeirarchyID - SQLServer 2012

    I was playing with hierarchy id a while back. This is what I found.
    Code:
    declare @hid hierarchyid;
    
    SELECT @hid = hid
    FROM x12.Segment
    WHERE id = 26;
    
    -- get all ancestors
    SELECT *
    FROM x12.Segment
    WHERE @hid.IsDescendantOf(hid) = 1
     AND @hid <> hid;
    
    -- get all descendants
    SELECT *
    FROM x12.Segment
    WHERE hid.IsDescendantOf(@hid) = 1;
    That is the very essence of human beings and our very unique capability to perform complex reasoning and actually use our perception to further our understanding of things. We like to solve problems. -Kleinma

    Does your code in post #46 look like my code in #45? No, it doesn't. Therefore, wrong is how it looks. - jmcilhinney

Posting Permissions

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



Click Here to Expand Forum to Full Width