Stack Overflow
Stack Overflow

Questions tagged [nested-sets]

The nested set model is a particular technique for representing nested sets (also known as trees or hierarchies) in relational databases.

From Wikipedia:

The nested set model is a particular technique for representing nested sets (also known as trees or hierarchies) in relational databases.

In the nested set model, nodes are numbered according to a tree traversal which visits each node twice, assigning numbers in the order of visiting, and at both visits. This leaves two numbers for each node, which are stored as two attributes. Querying becomes inexpensive: hierarchy membership can be tested by comparing these numbers. Updating requires renumbering and is therefore expensive. Refinements that use rational numbers instead of integers can avoid renumbering, and so are faster to update, although much more complicated.

392 questions
8
votes
2 answers

Which Hierarchical model should I use? Adjacency, Nested, or Enumerated?

I have a table which contains a location of all geographical locations in the world and their relationships. Here is a example that shows the hierarchy. You will see that the data is actually stored as all three Enumerated Path Adjacency…
Layke
  • 51,422
  • 11
  • 85
  • 111
8
votes
2 answers

Nested Set Query to retrieve all ancestors of each node

I have a MySQL query that I thought was working fine to retrieve all the ancestors of each node, starting from the top node, down to its immediate node. However when I added a 5th level to the nested set, it broke. Below are example tables, queries…
superphonic
  • 7,954
  • 6
  • 30
  • 63
8
votes
7 answers

How to render all records from a nested set into a real html tree

I'm using the awesome_nested_set plugin in my Rails project. I have two models that look like this (simplified): class Customer < ActiveRecord::Base has_many :categories end class Category < ActiveRecord::Base belongs_to :customer # Columns…
Christoph Schiessl
  • 6,818
  • 4
  • 33
  • 45
7
votes
3 answers

MySQL & nested set: slow JOIN (not using index)

I have two tables: localities: CREATE TABLE `localities` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `type` varchar(30) NOT NULL, `parent_id` int(11) DEFAULT NULL, `lft` int(11) DEFAULT NULL, `rgt` int(11)…
Igor Pavelek
  • 1,444
  • 14
  • 22
7
votes
3 answers

Dealing with nested sets in mysql?

I have decided to follow http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html So now I am looking for some help with the code. I am using their data for my testing, So, I visualized the tree being like so: array('value' => 'Richard…
Hailwood
  • 89,623
  • 107
  • 270
  • 423
7
votes
3 answers

BIGINT Out-of-range Error since MySQL 5.5

I'm working with nested sets for my CMS but since MySQL 5.5 I can't move a node. The following error gets thrown: Error while reordering docs:Error in MySQL-DB: Invalid SQL: SELECT baum2.id AS id, COUNT(*) AS level FROM elisabeth_tree AS…
user718790
  • 73
  • 1
  • 3
7
votes
1 answer

recursive query for adjacency list to preorder tree traversal in SQL?

I am migrating data from one database schema to another. The old schema has a categorization system based on an adjacency list, with id, category, and parent_id. If one category is under a second, that category has the second's id as its parent id.…
user151841
  • 17,377
  • 29
  • 109
  • 171
7
votes
4 answers

Nested routing in Ruby on Rails

My model class is: class Category < ActiveRecord::Base acts_as_nested_set has_many :children, :foreign_key => "parent_id", :class_name => 'Category' belongs_to :parent, :foreign_key => "parent_id", :class_name => 'Category' def to_param …
vooD
  • 2,881
  • 2
  • 25
  • 34
7
votes
1 answer

SQL Server Nested set vs Hierarchyid performance

I have a hierarchical data. The most common queries will be "get parent branch for node" and "get subtree of node". Updates and inserts are not likely to occur often. I am choosing between nested sets and hierarchyid. As far as I am concerned,…
Vasaka
  • 579
  • 6
  • 17
6
votes
0 answers

Nested Set Model or any kind of Hierarchical model in Entity Framework 4.2

I would like to know if there is some kind of plugin to manage nested set db design in entity framework 4.2 http://en.wikipedia.org/wiki/Nested_set_model I have hierarchical data to deal with, and in my mind, Nested set is the more effective way to…
Jean-Francois
  • 1,899
  • 4
  • 35
  • 73
6
votes
5 answers

How to create a sortable interface with 'acts as nested set' in RubyOnRails

I've been implementing some nice interactive interfaces that can sort lists in my m rails app for models that use acts_as_list. I have a sort function that gets called and sets the position for each record afterr each drag and drop using the …
Streamline
  • 2,040
  • 4
  • 37
  • 56
6
votes
2 answers

Indexes and Nested Sets

I am using a nested set to represent a hierarchy in my application, and am wondering where the best place to put indexes (clustered or otherwise) is. I am using Microsoft SQL Server 2008. Operations: About 40 times a day, a new hierarchy will be…
Moo-Juice
  • 38,257
  • 10
  • 78
  • 128
6
votes
0 answers

How to get Parent id of node in doctrine nested set

I have the following code : $treeObject = Doctrine_Core::getTable('Category')->getTree(); $rootColumnName = $treeObject->getAttribute('rootColumnName'); foreach ($treeObject->fetchRoots() as $root) { $options = array( 'root_id' =>…
wikus
  • 73
  • 4
6
votes
1 answer

Nested sets for Yii2 Invalid argument supplied for foreach()

I using https://github.com/creocoder/yii2-nested-sets extension for Yii2! So, first version for Yii1 was good but in this version i have some problem. All done strictly according to the manual! When created all models i earn error: This error…
Adobe
  • 558
  • 5
  • 23
6
votes
3 answers

Help with writing a SQL query for Nested Sets

I'm storing a tree in a DB using nested sets. The table's fields are id, lft, rgt, and name. Given a node ID, I need to find all of its direct children(not grandchildren) that are themselves leaf nodes.
Rusty
1
2
3
…
26 27