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
6
votes
6 answers

Searching for the best PHP nested sets class (PEAR class excluded)

I'm looking for a PHP (with MYSQL) nested sets class with all needed functions. For example: createLeftNode, createRightNode,createRootNode, createSubNode,deleteNode and moveTree. Not only 1 left, 1 right, 1 up and 1 down but also a part of a tree…
user35459
  • 63
  • 1
  • 1
  • 4
6
votes
3 answers

Store Hierarchical data in a best way: NoSQL or SQL

I am working hierarchical data, as in the tree structure. i want to know what is the best way to store them in database. I started with adjacency list, in MySQL. But the performance seems to dip as the data is increasing. I have around 20,000 rows…
Kushi
  • 63
  • 1
  • 4
6
votes
4 answers

How to SELECT immediate children and ancestors all in the same query

I'm working with a tree structure in MySQL that is respresented using the nested sets model. I'm hoping some of you sql experts can help me with building a SELECT query. I would like to be able to match a set of nodes using LIKE. For each node that…
Travis
  • 10,192
  • 4
  • 19
  • 19
6
votes
2 answers

Update nested sets when changing parent or deleting

I have the following table structure, which is also on sqlfiddle for…
aborted
  • 4,481
  • 14
  • 69
  • 132
5
votes
3 answers

Moving "root" or parent nodes - Doctrine NestedSet extension

I'm trying to replicate example from gedmo nested-set extension blog, where there are many parent nodes. There you are able to create as many movable parent nodes as well as children (which is typical for a nested set­Wikipedia). Reading trough…
Marko Jovanović
  • 2,647
  • 3
  • 27
  • 36
5
votes
1 answer

Nested sets, php array and transformation

I need to transform my nested sets structure (mysql) into json for this spacetree 1) http://blog.thejit.org/wp-content/jit-1.0a/examples/spacetree.html I found this function to create an array from nested sets: 2)…
EugenA
  • 323
  • 3
  • 14
5
votes
2 answers

Have you extended nested sets for hierarchical data modeling involving multiple parent nodes for a child node? What are your experiences?

I am looking to use this concept in one of my upcoming project. More info: Managing Hierarchical Data in MySQL. Please share your experiences good or bad with examples. I am adding more information to make it more broad: I have child items that can…
Srikar Doddi
  • 15,499
  • 15
  • 65
  • 106
5
votes
2 answers

Problem joining tables in SQL

SELECT MID, FAD.FirstOpenedDate ,LCD.LastCloseDate FROM mwMaster.dbo.Merchant M JOIN ( SELECT MerchID, MIN(moddate) AS FirstOpenedDate FROM mwMaster.dbo.MerchantStatusHistory GROUP BY MerchID ) …
JBone
  • 3,163
  • 11
  • 36
  • 47
5
votes
1 answer

rails ancestry nested form

I've just started using Ancestry instead of awesome_nested_set and I would like to create a nested form so that I can create a parent account and many children accounts all in the one form. The problem seems to be that Ancestry doesn't allow you to…
map7
  • 5,096
  • 6
  • 65
  • 128
5
votes
2 answers

PHP RecursiveIteratorIterator and nested sets

I have a set of objects in a hierachy. There's a top "root" node and that has child nodes, which in turn have child nodes etc. I'm trying to save this structure into a DB using the nested set model, where each "side" of each node is numbered to…
Jack Sleight
  • 17,010
  • 6
  • 41
  • 55
5
votes
2 answers

How to display a form for a subset of associated records, some of which don't exist yet?

I have Tasks and Users. When a user completes a task, I create a Completion which has a field for the user to indicate how long they spent. I need a form that shows all the tasks with their completion status and time_spent attribute. On submit,…
Turadg
  • 7,471
  • 2
  • 48
  • 49
5
votes
2 answers

Doctrine 2 nested set - retrieve full tree in single query

I'm using stof/StofDoctrineExtensionsBundle (Bundle wrapper for Atlantic18/DoctrineExtensions) to implement a Nested Set (tree) entity. The entity is configured and working, but I can't figure out how to retrieve all root notes with all of their…
James Crinkley
  • 1,398
  • 1
  • 13
  • 33
5
votes
3 answers

Ruby on Rails - Awesome nested set plugin

Is there a simple way to display the entire nested set when using this plugin? What I would like to do is display an unordered list of root nodes with another unordered list inside each child that also contains children and so on? Any advice…
Dan
  • 703
  • 1
  • 7
  • 11
4
votes
4 answers

PHP: Sort an array

I've got an array with data from a MySQL table in nested set model I'd like to get sorted, not only alphabetical but also with the child nodes directly after the parent node. Example - array to be sorted (before the sorting): Array ( [0] =>…
Ivar
  • 4,344
  • 6
  • 38
  • 53
4
votes
6 answers

Finding breadcrumbs for nested sets

I'm using nested sets (aka modified preorder tree traversal) to store a list of groups, and I'm trying to find a quick way to generate breadcrumbs (as a string, not a table) for ALL of the groups at once. My data is also stored using the adjacency…
gregmac
  • 24,276
  • 10
  • 87
  • 118
1 2
3
…
26 27