Nested Set Trees

Store and query hierarchical data with the first-party nestedset extension.

extensions/nestedset provides a typed tree service for hierarchical data. It stores each node with ParentID, _lft, _rgt, and Depth, then exposes explicit methods for creating, moving, reading, deleting, checking, and rebuilding trees.

Install

import "github.com/duxweb/oro/extensions/nestedset"

db, err := oro.Open(oro.Config{
    Connections: map[string]oro.ConnectionConfig{
        "default": {Driver: sqlite.Open("app.db")},
    },
    Extensions: []oro.Extension{
        nestedset.Extension(),
    },
})

Model

Embed nestedset.NodeFields:

type Category struct {
    oro.Model
    nestedset.NodeFields

    Name string
}

func (Category) Define(s *oro.SchemaBuilder) {
    s.Table("categories")
    s.Field("Name").String().Size(120)
}

Default columns:

Field	Column
`ParentID`	`parent_id`
`Lft`	`_lft`
`Rgt`	`_rgt`
`Depth`	`depth`

If you need custom field names, use nestedset.FieldNames(parent, left, right, depth) in both Define and Use.

Create nodes

tree := nestedset.Use[Category](db)

root, err := tree.Create(ctx, &Category{Name: "Catalog"})
phones, err := tree.Create(ctx, &Category{
    Name: "Phones",
    NodeFields: nestedset.NodeFields{
        ParentID: oro.NullOf(root.ID),
    },
})

Create reads ParentID from the model. Empty ParentID creates a root node; a valid ParentID creates the model as the last child of that parent.

You can also use explicit placement methods:

root, err := tree.CreateRoot(ctx, &Category{Name: "Catalog"})
phones, err := tree.CreateChild(ctx, root.ID, &Category{Name: "Phones"})
_, err = tree.CreateFirstChild(ctx, phones.ID, &Category{Name: "Android"})
_, err = tree.CreateAfter(ctx, phones.ID, &Category{Name: "Laptops"})

CreateRoot appends a new root tree.
CreateChild appends as the last child.
CreateFirstChild inserts as the first child.
CreateBefore and CreateAfter insert as siblings.

Structural writes run in transactions.

Plain ORM writes such as db.Use[Category]().Create(...) do not maintain nested-set bounds. Use the tree service for tree-aware writes.

Update nodes

phones.Name = "Smart Phones"
phones.ParentID = oro.NullOf(newParentID)

updated, err := tree.Update(ctx, phones)

Update writes only the fields that are set (non-zero) on the model; it no longer overwrites unset or other columns with their zero values. It compares ParentID with the current database row, and if ParentID changed, the subtree is moved and _lft, _rgt, and Depth are recalculated.

To update specific columns by id without rehydrating the whole struct, use UpdateValues:

updated, err := tree.UpdateValues(ctx, node.ID, oro.Map{"Name": "New name"})

func (tree *Tree[T]) UpdateValues(ctx context.Context, nodeID any, values oro.Map, options ...oro.WriteOption) (*T, error)

Because Update skips zero values, use UpdateValues with an oro.Map when you need to set a field to a zero value explicitly.

For position-specific moves, use the explicit move methods below.

Read trees

roots, err := tree.Roots(ctx)
all, err := tree.All(ctx)
nested, err := tree.Tree(ctx)
subtree, err := tree.Subtree(ctx, phones.ID)
flatSubtree, err := tree.FlatSubtree(ctx, phones.ID)

Tree and Subtree return nested nodes:

type Node[T any] struct {
    Model    *T
    Children []*Node[T]
}

Read relationships

parent, err := tree.Parent(ctx, nodeID)
children, err := tree.Children(ctx, nodeID)
first, err := tree.FirstChild(ctx, nodeID)
last, err := tree.LastChild(ctx, nodeID)
siblings, err := tree.Siblings(ctx, nodeID)
prev, err := tree.PrevSibling(ctx, nodeID)
next, err := tree.NextSibling(ctx, nodeID)
ancestors, err := tree.Ancestors(ctx, nodeID)
ancestorsAndSelf, err := tree.AncestorsAndSelf(ctx, nodeID)
descendants, err := tree.Descendants(ctx, nodeID)
descendantsAndSelf, err := tree.DescendantsAndSelf(ctx, nodeID)

Single-node reads return nil, nil when not found. Multi-node reads return an empty slice.

Relative depth

Database Depth is absolute inside the whole tree. For business UIs such as distribution trees, use relative depth helpers where the current node is 0, direct children are 1, and grandchildren are 2.

rows, err := tree.DescendantsWithDepth(ctx, nodeID)
rows, err := tree.DescendantsAndSelfWithDepth(ctx, nodeID)

They return:

type RelativeNode[T any] struct {
    Model *T
    Depth int // relative depth
}

Limit descendants by relative depth:

items, err := tree.DescendantsWithinDepth(ctx, nodeID, 2) // children and grandchildren
items, err := tree.DescendantsAtDepth(ctx, nodeID, 1)     // direct children only

DescendantsAndSelfWithinDepth and DescendantsAndSelfAtDepth include the current node. These methods still use SQL bounds and depth conditions; they do not load the whole table first.

Predicates

ok, err := tree.IsRoot(ctx, nodeID)
ok, err := tree.IsLeaf(ctx, nodeID)
ok, err := tree.IsAncestorOf(ctx, ancestorID, nodeID)
ok, err := tree.IsDescendantOf(ctx, nodeID, ancestorID)

Move nodes

err := tree.MoveToRoot(ctx, nodeID)
err := tree.MoveToChildOf(ctx, nodeID, parentID)
err := tree.MoveToFirstChildOf(ctx, nodeID, parentID)
err := tree.MoveBefore(ctx, nodeID, targetID)
err := tree.MoveAfter(ctx, nodeID, targetID)
err := tree.MoveUp(ctx, nodeID)
err := tree.MoveDown(ctx, nodeID)

A node cannot move into itself or its own descendants. Moves preserve the full subtree.

Delete subtrees

deleted, err := tree.Delete(ctx, nodeID)
deleted, err := tree.DeleteSubtree(ctx, nodeID)

Delete and DeleteSubtree both delete the current node and all descendants, then close the tree gap.

Scoped trees

Use Scope when one table contains multiple independent trees, such as tenant trees or app-specific trees:

tree := nestedset.Use[Category](db, nestedset.Scope(oro.Map{
    "TenantID": uint64(1),
}))

The scope is applied to every read and structural write. Scope values are also written into newly created models.

Unified Apply queries

For advanced reads, use the core model query with Apply:

nodes, err := db.Use[Category]().
    Apply(nestedset.DepthGte(1), nestedset.DefaultOrder()).
    Get(ctx)

items, err := db.Use[Category]().
    Apply(nestedset.DescendantsOf(root), nestedset.DefaultOrder()).
    Where("Status", "active").
    Get(ctx)

DescendantsOf(root) can receive the current model; the extension reloads the latest bounds by model ID, so stale _lft/_rgt values on an old struct are not used.

Supported apply helpers include Roots, AncestorsOf, AncestorsAndSelfOf, DescendantsOf, DescendantsAndSelfOf, DescendantsWithinDepthOf, DescendantsAtDepthOf, SiblingsOf, Depth, DepthGte, DepthLte, DefaultOrder, and Reversed.

tree.Query() remains as a compatibility wrapper, but new code should prefer db.Use[Category]().Apply(...) so extension packages do not maintain their own Where/Get/Limit API.

Check and rebuild

result, err := tree.Check(ctx)
if err != nil {
    return err
}
if !result.Valid {
    err = tree.Rebuild(ctx)
}

Rebuild recalculates Lft, Rgt, and Depth from ParentID.

You can also persist an explicit nested payload:

err := tree.RebuildTree(ctx, []*nestedset.Node[Category]{
    {
        Model: &Category{Name: "Root"},
        Children: []*nestedset.Node[Category]{
            {Model: &Category{Name: "Child"}},
        },
    },
})

New models in the payload are inserted. Existing models with an ID are updated.