Skip to content

Latest commit

 

History

History
118 lines (102 loc) · 5.84 KB

README.md

File metadata and controls

118 lines (102 loc) · 5.84 KB

Gorm hierarchyid

  • Library to handle hierarchyid type in SQL Server and go.
    • Generation and parsing of hierarchyid type in go.
    • Type wrapper for usage with gorm ORM.
  • The hierarchyid is data to represent a position in a hierarchy in SQL Server.
    • It is a variable length type with reduced storage requirements.
  • Encodes the position in the hierarchy as a list of indexes
    • For example in the tree below the path to E is /1/1/2/
    • Indexes can be used to sort elements inside of a tree level.

How it works

  • The HierarchyID is defined as a []int64 in go.
  • When serialized into JSON a textual representation is used for readability.
    • Represented as list separated by /. (e.g. /1/2/3/4/5/)
  • Each element in the slice represents a level in the hierarchy.
  • An empty slice represents the root of the hierarchy.
    • Elements placed in the root should not use an empty list.
    • They should instead by represented by /1/, /2/, etc.

Installation

  • The library can be installed using go get.
go get github.com/tentone/hierarchyid

Model definition

  • Declare HierarchyID type in your gorm model, there is no need to specify the DB data type.
  • Is is recommended to also mark the field as unique to avoid duplicates.
  • The library will handle the serialization and deserialization of the field to match the SQL Server hierarchyid type. 
    type Model struct {
    gorm.Model

    Path hierarchyid.HierarchyID `gorm:"unique;not null;"`
}
  • In some scenarios it might be usefull to also keep a tradicional relationship to the parent.
    • This can be done by adding a ParentID field to the model.
    • It ensures that the tree is consistent and that actions (e.g. delete) are cascaded to the children.
    • Some operations might also be easier to perform with the parent relationship.
    type Model struct {
  gorm.Model

  Path hierarchyid.HierarchyId `gorm:"unique;not null;"`

  ParentID uint              `gorm:"index"`
  Parent   *TestParentsTable `foreignKey:"parent_id;references:id;constraint:OnUpdate:NO ACTION,OnDelete:CASCADE;"`
}

Usage

Create

  • Elements can be added to the tree as regular entries
  • Just make sure that the tree indexes are filled correctly, indexes dont need to be sequential.
db.Create(&Table{Path: hierarchyid.HierarchyID{Data: []int64{1}}})
db.Create(&Table{Path: hierarchyid.HierarchyID{Data: []int64{1, 1}}})
db.Create(&Table{Path: hierarchyid.HierarchyID{Data: []int64{1, 1, 2}}})

Get Ancestors

  • To get all parents of a node use the GetAncestors method.
  • The method will return a slice with all the parents of the node. This can be used as param for a query.
db.Model(&Table{}).Where("[path] IN (?)", child.Path.GetAncestors()).Find(&parents)
  • Its also possible to get parents with the SQL version of the GetAncestor method.
  • Example on getting the parent of an element.
db.Model(&Table{}).Where("[path] = ?.GetAncestor(1)", child.Path).Find(&parent)

Get Descendants

  • To get all children of a node use the IsDescendantOf method in SQL.
  • Example on getting all children of a node (including the node itself).
elements := []Table{}
db.Where("[path].IsDescendantOf(?)=1", hierarchyid.HierarchyId{Data: []int64{1, 2}}).Find(&elements)
  • It is also possible to filter the children based on sub-levels.
  • Example on getting all nodes from root where at least one of the sub-level has a name that contains the text 'de'
SELECT *
FROM "table" as a
WHERE ([path].GetLevel()=1 AND [path].IsDescendantOf('/')=1) AND
(SELECT COUNT(*) FROM "table" AS b WHERE b.path.IsDescendantOf(a.path)=1 AND b.name LIKE '%de%')>0
  • The GetLevel method can be used to filter nodes based on their level in the hierarchy. Also available in SQL with the same name GetLevel.
  • A more generic version of the same code presented above writen in go.
root := hierarchyid.GetRoot()
subQuery := db.Table("table AS b").Select("COUNT(*)").Where("[b].[path].IsDescendantOf([a].[path])=1 AND [b].[name] LIKE '%de%'")
conn = db.Table("table AS a").
  Where("[a].[path].GetLevel()=? AND [a].[path].IsDescendantOf(?)=1 AND (?)>0", root.GetLevel()+1, root, subQuery).
  Find(&elements)

Move nodes

  • To move a node to a new parent there is the GetReparentedValue method that receives the old parent and new parent and calculates the new hierarchyid value.
  • Example on moving a node to a new parent.
db.Model(&Table{}).Where("[id] = ?", id).Update("[path]=?", node.Path.GetReparentedValue(oldParent.Path, newParent.Path))

Resources

License

  • The project is distributed using a MIT license. Available on the project repository.