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
4
votes
1 answer

Sorting / Grouping records for pagination with many association-based restrictions

I'm working on an application which acts as a places directory and (currently, but maybe not always) allows filtering of a particular businesses' venues based upon 1 city, 1 neighborhood, and 1 category. Categories can be nested, and therefore they…
Adam Eberlin
  • 14,005
  • 5
  • 37
  • 49
4
votes
1 answer

How many "entities" are beneath this hierarchical structure. Nested Set/Adjacency (How many POI are inside of England)

Development language and DB: PHP/MySQL I have a table geo_places with something like 8 million geographical locations. These places are all hierarchical, and I use parent_id (adjacency), lft/rgt (nested set) and ancestry (enumerated). Now, I…
Layke
  • 51,422
  • 11
  • 85
  • 111
4
votes
1 answer

Is a table lock required to maintain data integrity in the following situation

In MySQL, I have a tree represented by a nested set. Manipulation of the nested set requires modifying multiple rows in the table. For example, adding a node to the tree will require reordering the right and left values... SELECT @myLeft := lft…
Trev
  • 58
  • 3
4
votes
1 answer

MySQL Subquery Looping for each row returned

This uses two queries and I like to get result for each rows returned in the first subquery that will run the second query, essentially this will act like a foreach loop. I hope there is someone who can figure this out quite quickly. CREATE TABLE…
Rajib Hossain Pavel
  • 438
  • 7
  • 22
4
votes
3 answers

Ordering category select menu alphabetically with awesome_nested_set

Using awesome_nested_set with Rails 3, I've created a hierarchical categories system. To display the category selector in the view, I've used the following code: <%= form.select :parent_id, options_for_select(nested_set_options(Category, @category)…
Chris Alley
  • 3,015
  • 2
  • 21
  • 31
4
votes
5 answers

Move node in Nested Sets tree

I am working on an adjacency list with mySQL and can not (atleast by myself) do the thinking needed to make a decent enough query to be able to move a set of nodes (together with eventual children nodes) around. The table has following columns: id …
Industrial
  • 41,400
  • 69
  • 194
  • 289
4
votes
1 answer

How to Query Nested Set model with multiple roots plus filtering

How do you query a nested set model with multiple roots, such trees in the same table? Currently, I added an extra column called the "Root" indicating the ID of the root node for all sub-tree nodes, however, I can't figure out the sql to retrieve…
Liming
  • 1,641
  • 3
  • 28
  • 38
4
votes
3 answers

Yii nested set to dropdown menu

I'm using Yii nested set behavior, which helps me to keep my categories nested as seen here (nevermind title rows, they are in russian): And all I want to do is to have Bootstrap nested menu, which should be like this: $criteria = new…
Max Filippov
  • 2,024
  • 18
  • 37
4
votes
1 answer

Testing model that acts_as_nested_set with RSpec

I have an Organization model that acts_as_nested_set, using awesome_nested_set: class Organization < ActiveRecord::Base acts_as_nested_set attr_accessible :name, :location_id, :parent_id has_many :org_prod_relationships, dependent: :destroy …
thebenedict
  • 2,539
  • 3
  • 20
  • 29
3
votes
0 answers

Saving graph into MySQL database

I have a problem storing and querying graph to SQL database. I have read some tutorials about storing trees into relational db, but my graph is kinda diferent. You can see sample graph in my picture https://i.stack.imgur.com/J57v6.png. It has root…
Tomas Zaruba
  • 196
  • 1
  • 8
3
votes
3 answers

Invert nested dictionaries in f# Map<'a,Map<'b,'T>>) -> Map<'b,Map<'a,'T>>

I have a nested dictionary Map<'a,Map<'b,'T>>, so that for a combination of a*b, the entry is unique. In order to precompute efficiently, I would need to invert the keys in a Map<'b,Map<'a,'T>> I have some higher order methods that do the job (|/>…
nicolas
  • 9,549
  • 3
  • 39
  • 83
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
1 answer

Nested set INSERT operation for SQLite

I am trying to implement the nested-set model for a sqlite database. So far I have implemented getting leaf nodes, finding subordinates etc. I am using this tutorial as a reference However, I am stuck at inserting a new node. Here's the code from…
C.d.
  • 9,932
  • 6
  • 41
  • 51
3
votes
4 answers

How can I update a nested set tree structure?

I've looked at Managing Hierarchical Data in MySQL, but it really only deals with adding and deleting nodes in a Nested Set Model. I need to be able to move nodes with and without child nodes. How would I do this?
Andre
3
votes
1 answer

Rails3: Avoiding select n+1 with the Ancestry Gem?

Requirements: I am building a task list application and wanted tasks to be able to have sub-tasks. I also wanted tasks to be able to exist in multiple places in the tree at once, for example, if I had the 2 tasks: Build dog kennel Put up new…
Steve Occhipinti
  • 478
  • 5
  • 12
1 2 3
…
26 27