Stack Overflow
Stack Overflow

Questions tagged [nested-set-model]

Nested Set is a method for saving hierarchical data in a relational database

Nested Set is a method used to save hierarchical data in a relational database like MySQL, Postgresql, Oracle, etc ... The term is coined by Joe Celko in his book "Trees and Hierarchies in SQL"

Nested set also goes by the term "Modified Preorder Tree Traversal".

http://en.wikipedia.org/wiki/Nested_set_model

52 questions
24
votes
1 answer

Adjacency List Model vs Nested Set Model for MySQL hierarchical data?

There are two ways to work with hierarchy data in MySQL: Adjacency List Model Nested Set Model A major problem of the Adjacency List Model is that we need to run one query for each node to get the path of the hierarchy. In the Nested Set Model…
Gustavo Piucco
  • 377
  • 1
  • 3
  • 9
9
votes
6 answers

Is there a simple way to query the children of a node?

I've been using the crap out of the Nested Set Model lately. I have enjoyed designing queries for just about every useful operation and view. One thing I'm stuck on is how to select the immediate children (and only the children, not further…
Adam Siler
  • 1,986
  • 5
  • 22
  • 26
6
votes
2 answers

Adjacency list vs. nested set model

I have been looking into Adjacency List and Nested Set Model to find the optimal tree solution. Up until now I thought one of the main advantages of Nested Set Model was that I could use one SQL query and some code to get a complete tree. But it is…
Cudos
  • 5,733
  • 11
  • 50
  • 77
6
votes
5 answers

How do I format Nested Set Model data into an array?

Let's dig in the main problem right away, I have the input like this $category = array( 'A' => array('left' => 1, 'right' => 8), 'B' => array('left' => 2, 'right' => 3), 'C' => array('left' => 4, 'right' => 7), 'D' => array('left' => 5,…
quocnguyen.clgt
  • 108
  • 1
  • 6
5
votes
3 answers

Nested Set Model Php library

Hi I need to use the nested set model to mange product categories on my site. Does anyoune know of some good pre-built PHP libraries for handling nested sets in MySQL?
andrew
  • 5,096
  • 10
  • 43
  • 57
4
votes
1 answer

How do I update a simple_form input field using AJAX?

I have a simple_form input field that is part of a form that I need to update asynchronously when the onchange event is triggered. The field that I need to update is displayed as a select box since it is a collection. But the collection represents…
Steve S
  • 509
  • 1
  • 11
  • 24
4
votes
1 answer

Bulk update with subquery using SQLAlchemy

I'm trying to implement the following MySQL query using SQLAlchemy. The table in question is nested set hierarchy. UPDATE category JOIN ( SELECT node.cat_id, (COUNT(parent.cat_id) - 1) AS depth FROM category AS node,…
PartialOrder
  • 2,870
  • 3
  • 36
  • 44
3
votes
1 answer

jOOQ complex update - how to lock the table?

here is a "add node" SQL query for nested set model LOCK TABLE mytestdb.tbltree WRITE; SELECT @myRight := rgt FROM mytestdb.tbltree WHERE name = 'apples'; UPDATE mytestdb.tbltree SET rgt = rgt + 2 WHERE rgt > @myRight; UPDATE mytestdb.tbltree SET…
Charis997
  • 335
  • 1
  • 6
  • 11
3
votes
5 answers

jquery add new li to nested set model

What is the best way to insert a new li element into a specific position of a nested set model? For example, the following list:
    1. …
eduardev
  • 473
  • 8
  • 26
3
votes
0 answers

How to create a MySQL view to show all paths for each node in a nested set model through a related table?

I have two tables, categories and products. Categories table is a nested set model. Products table has a serial_number field that is unique. Their schema is like this : Categories : +----+-----------+-----+-----+-------+-------------+ | id |…
Amir
  • 413
  • 4
  • 13
3
votes
1 answer

Find Lowest Common Ancestor in a Nested Set

I am looking for a way to find the Lowest Common Ancestor within a Nested Set can be found using a single equation. For example, from the image at: https://commons.wikimedia.org/wiki/File:Clothing-hierarchy-traversal.svg The LCA between Suits and…
Flosculus
  • 6,880
  • 3
  • 18
  • 42
3
votes
3 answers

Sorting nested set by name while keep depth integrity

I'm using the nested set model that'll later be used to build a sitemap for my web site. This is my table structure. create table departments ( id int identity(0, 1) primary key , lft int , rgt int , name nvarchar(60) ); insert into…
Mike
  • 4,257
  • 3
  • 33
  • 47
3
votes
1 answer

Sorting siblings in nested set

I have a nested set model in SQLite database. The siblings need to be sorted alphabetically. Below is the full set, but the siblings that need to be sorted is the second level. As you can see, they start in this order: Kanval, Wafiyah, Qamar,…
Sam Carleton
  • 1,339
  • 7
  • 23
  • 45
2
votes
1 answer

Create JSON for Fancytree from a nested set model structure

I would like to display a data structure with the jquery plugin Fancytree. After a long search in Google, it seems that the best way to store a data structure in a database is the Nested Set Model (described on Stackoverflow here php / Mysql best…
Pierre
  • 1,044
  • 15
  • 27
2
votes
1 answer

Nested sets how to determine the left and right nodes

1 - Root - 20 2 - child1 - 3 4 - child2 - 5 6 - child3 - 7 8 - child4 - 9 This is a nested set model of mysql. let's say that the _lft and _rgt are the fields which indicates the left and right values as I see the lower the value is the…
Rustam
  • 249
  • 3
  • 11
1
2 3 4