Questions tagged [hierarchical-data]
Hierarchical data reflects a set of parent-child relationships. These can be found in a genealogy, a taxonomy, a list of requirements for parts assembly, and innumerably other instances. Methods for dealing with hierarchical data are often essential for data management and analysis.
2,061
questions
1598
votes
8
answers
338k
views
What are the options for storing hierarchical data in a relational database?
Good Overviews
Generally speaking, you're making a decision between fast read times (for example, nested set) or fast write times (adjacency list). Usually, you end up with a combination of the ...
577
votes
15
answers
141k
views
What is the most efficient/elegant way to parse a flat table into a tree?
Assume you have a flat table that stores an ordered tree hierarchy:
Id Name ParentId Order
1 'Node 1' 0 10
2 'Node 1.1' 1 10
3 'Node 2' 0 ...
- 335k
449
votes
17
answers
454k
views
How to create a MySQL hierarchical recursive query?
I have a MySQL table which is as follows:
id
name
parent_id
19
category1
0
20
category2
19
21
category3
20
22
category4
21
...
...
...
Now, I want to have a single MySQL query to which I simply ...
- 4,735
200
votes
22
answers
127k
views
How to build a tree from a flat structure?
I have a bunch of objects in a flat structure. These objects have an ID and a ParentID property so they can be arranged in trees. They are in no particular order.
Each ParentID property does not ...
- 5,940
121
votes
7
answers
134k
views
Simplest way to do a recursive self-join?
What is the simplest way of doing a recursive self-join in SQL Server?
PersonID | Initials | ParentID
1 CJ NULL
2 EB 1
3 MB 1
4 SW 2
...
- 3,181
109
votes
11
answers
76k
views
Convert a series of parent-child relationships into a hierarchical tree?
I have a bunch of name-parentname pairs, that I'd like to turn into as few heirarchical tree structures as possible. So for example, these could be the pairings:
Child : Parent
H : G
F : G
...
- 96.6k
89
votes
8
answers
52k
views
Resampling Within a Pandas MultiIndex
I have some hierarchical data which bottoms out into time series data which looks something like this:
df = pandas.DataFrame(
{'value_a': values_a, 'value_b': values_b},
index=[states, cities,...
- 1,010
78
votes
13
answers
62k
views
What type of NoSQL database is best suited to store hierarchical data?
What type of NoSQL database is best suited to store hierarchical data?
Say for example I want to store posts of a forum with a tree structure:
original post
+ re: original post
+ re: original post
...
- 90.7k
69
votes
9
answers
73k
views
How to represent a data tree in SQL?
I'm writing a data tree structure that is combined from a Tree and a TreeNode. Tree will contain the root and the top level actions on the data.
I'm using a UI library to present the tree in a windows ...
- 1,019
58
votes
9
answers
41k
views
Is it possible to query a tree structure table in MySQL in a single query, to any depth?
I'm thinking the answer is no, but I'd love it it anybody had any insight into how to crawl a tree structure to any depth in SQL (MySQL), but with a single query
More specifically, given a tree ...
- 6,952
55
votes
3
answers
7k
views
Backbone with a tree view widget
I'm evaluating the Backbone javascript framework for use in a project that will display a hierarchical model in a tree view widget (think the Windows file browser).
I love how Backbone thinks about ...
- 2,382
54
votes
4
answers
15k
views
Is it a good idea to use MySQL and Neo4j together?
I will make an application with a lot of similar items (millions), and I would like to store them in a MySQL database, because I would like to do a lot of statistics and search on specific values for ...
- 125k
53
votes
12
answers
45k
views
Nice & universal way to convert List of items to Tree
I have list of categories:
╔════╦═════════════╦═════════════╗
║ Id ║ Name ║ Parent_id ║
╠════╬═════════════╬═════════════╣
║ 1 ║ Sports ║ 0 ║
║ 2 ║ Balls ║ 1 ║...
user2930009
53
votes
5
answers
52k
views
basic recursive query on sqlite3?
I have a simple sqlite3 table that looks like this:
Table: Part
Part SuperPart
wk0Z wk00
wk06 wk02
wk07 wk02
eZ01 eZ00
eZ02 eZ00
eZ03 eZ01
eZ04 eZ01
I need to run a recursive ...
- 3,311
43
votes
4
answers
4k
views
Oracle SYS_CONNECT_BY_PATH equivalent query into SQL Server
I'm trying to convert a complex query involving the Oracle SYS_CONNECT_BY_PATH syntax into SQL Server:
SELECT
DISTINCT TO_CHAR(CONCAT(@ROOT, SYS_CONNECT_BY_PATH(CONCAT('C_',X), '.'))) ...
- 1,345
40
votes
1
answer
26k
views
WPF Treeview Databinding Hierarchal Data with mixed types
I have a bit of a complex situation with WPF Treeview Binding. I have spent the last 2 days trying Google it, and this is the closed I came up with, but it doesn't solve the issue.
Here is the ...
- 1,982
36
votes
3
answers
19k
views
Hierarchical/tree database for directories path in filesystem
I want to store the directories (present on the disk) into a database, maintaining their hierarchical/tree structure.
Here's a figure:
(ROOT)
/ ...
- 587
35
votes
5
answers
113k
views
SQL recursive query on self referencing table (Oracle)
Lets assume I have this sample data:
| Name | ID | PARENT_ID |
-----------------------------
| a1 | 1 | null |
| b2 | 2 | null |
| c3 | 3 | null |
| a1.d4 | ...
- 29.7k
35
votes
2
answers
34k
views
How to represent a tree like structure in a db
I'm starting a project and I'm in the designing phase: I.e., I haven't decided yet on which db framework I'm going to use. I'm going to have code that creates a "forest" like structure. That is, many ...
- 14.5k
33
votes
9
answers
26k
views
Getting all parent rows in one SQL query
I have a simple MySQL table thats contains a list of categories, level is determined by parent_id:
id name parent_id
---------------------------
1 Home 0
2 About 1
3 Contact ...
- 6,627
31
votes
4
answers
29k
views
Generating Depth based tree from Hierarchical Data in MySQL (no CTEs)
Hi For many days I have been working on this problem in MySQL, however I can not figure it out. Do any of you have suggestions?
Basically, I have a category table with domains like: id, name (name of ...
- 1,029
30
votes
10
answers
4k
views
Is there a way to convert CSV columns into hierarchical relationships?
I have a csv of 7 million biodiversity records where taxonomy levels are as columns. For instance:
RecordID,kingdom,phylum,class,order,family,genus,species
1,Animalia,Chordata,Mammalia,Primates,...
29
votes
6
answers
45k
views
Fatal error: Nesting level too deep - recursive dependency?
I have a complex hierarchy of nested objects, with all of the child objects (stored an array of objects in the parent class) containing a property linking back to their parent: fairly simple and ...
- 211k
28
votes
4
answers
21k
views
How to store a tree in SQL database
I have to store a tree in a database, so what is the best way to do this? Show the method you use and name its pros and cons. (I am using SQL Server 2005)
- 1,782
26
votes
9
answers
27k
views
Expressing recursion in LINQ
I am writing a LINQ provider to a hierarchal data source. I find it easiest to design my API by writing examples showing how I want to use it, and then coding to support those use cases.
One thing I ...
- 143k
25
votes
13
answers
24k
views
Move node in nested set
I'd need a MySQL query that moves a node and all its children within a nested set. I found this site, but that function just seems so illogical - there's no universeid or treeid in a nested set model, ...
- 4,384
24
votes
3
answers
7k
views
Storing hierarchical data in Google App Engine Datastore?
Can someone illustrate how I can store and easily query hierarchical data in google app engine datastore?
- 1,321
23
votes
7
answers
20k
views
C# algorithm for generating hierarchy
I've got a text file that looks like this:
{ Id = 1, ParentId = 0, Position = 0, Title = "root" }
{ Id = 2, ParentId = 1, Position = 0, Title = "child 1" }
{ Id = 3, ParentId = 1, Position = 1, Title ...
- 59.5k
23
votes
2
answers
17k
views
Counting number of children in hierarchical SQL data
for a simple data structure such as so:
ID parentID Text Price
1 Root
2 1 Flowers
3 1 Electro
4 2 Rose 10
5 2 ...
- 21.6k
23
votes
2
answers
10k
views
Smooth transitioning between tree, cluster, radial tree, and radial cluster layouts
For a project, I need to interactively change hierarchical data layout of a visualization - without any change of the underlying data whatsoever. The layouts capable of switching between themselves ...
- 10.5k
21
votes
3
answers
35k
views
php / Mysql best tree structure
I have to build a tree that will contain about 300 nodes inside it. The tree has no depth limitations. So it can have 3 or 15 levels. Each node can have an unlimited number of children.
The priority ...
- 863
21
votes
2
answers
51k
views
Sql query for tree table
I have a table with tree structure:
id parentId name
----------------
1 0 Category1
2 0 Category2
3 1 Category3
4 2 Category4
5 1 Category5
6 2 ...
- 6,925
21
votes
1
answer
8k
views
Sorting a subtree in a closure table hierarchical-data structure
I would like to ask you to help me with the problem with sorting of the hierarchical data structure stored as a closure table.
I wanted to use this structure to store my website menu. Everything ...
- 430
19
votes
8
answers
49k
views
MySQL Recursive get all child from parent
i have this case using recursive query on Mysql to find lv 2 and lv3 child on one table...
database structure i'm using:
id name parent
1 A 0
2 B 0
3 C 0
4 D 1
5 E 1
6 ...
- 477
17
votes
6
answers
10k
views
How can I break referential integrity briefly, within a transaction, without disabling the foreign key constraint?
I have a table with 3 columns:
ID, PARENT_ID, NAME
PARENT_ID has a foreign key relationship with ID in the same table. This table is modeling a hierarchy.
Sometimes the ID of a record will change. ...
- 8,841
17
votes
2
answers
5k
views
efficient function to retrieve a queryset of ancestors of an mptt queryset
Does anybody have an efficient algorithm to retrieve all ancestors of an mptt queryset? The best I could think of so far is something like this:
def qs_ancestors(queryset):
if isinstance(queryset,...
- 2,165
16
votes
1
answer
13k
views
MySQL Closure Table hierarchical database - How to pull information out in the correct order
I have a MySQL database holding hierarchical data using the Closure Table method. A simple sample database create script follows the question. My issue at the moment is how do I pull data out of the ...
- 21.3k
15
votes
4
answers
24k
views
(ID/ParentID) list to Hierarchical list
MyClass consists of ID ParentID and List<MyClass> as Children
I have list of MyClass like this
ID ParentID
1 0
2 7
3 1
4 5
5 1
6 2
7 1
8 6
9 0
10 9
Output (Hierarchical ...
- 7,110
15
votes
10
answers
9k
views
Achieve hierarchy, Parent/Child Relationship in an effective and easy way
I have a table like
create table site
(
site_Id int(5),
parent_Id int(5),
site_desc varchar2(100)
);
Significance of the fields:
site_Id : Id of the sites
parent_Id : Parent id of the site
site_desc ...
- 13.4k
15
votes
8
answers
5k
views
get parents and children of tree folder structure in my sql < 8 and no CTEs
I have a folder table that joins to itself on an id, parent_id relationship:
CREATE TABLE folders (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
title nvarchar(255) NOT NULL,
parent_id int(10) ...
- 27.9k
15
votes
2
answers
10k
views
What tool generates diagrams from SQL Server hierarchical data?
Is there a tool that works with SQL Server to generate tree-like diagrams from a hierachical data model?
I am working with a large geographical hierarchy, and would like to visualize it.
Here is an ...
- 20.4k
15
votes
2
answers
4k
views
Managing hierarchies in SQL: MPTT/nested sets vs adjacency lists vs storing paths
For a while now I've been wrestling with how best to handle hierarchies in SQL. Frustrated by the limitations of adjacency lists and the complexity of MPTT/nested sets, I began thinking about simply ...
- 179k
15
votes
2
answers
5k
views
hierarchical data in a database: recursive query vs. closure tables vs. graph database
I'm starting on a new project that has some hierarchical data and I'm looking at all the options for storing that in a database at the moment.
I am using PostgreSQL, which does allow recursive ...
- 668
15
votes
1
answer
3k
views
Is there a practical way to use the hierarchyID datatype in entity framework 4?
As it stands now, the CLR UDTs including HierarchyID aren't supported in Entity Framework 4. HierarchyID.ToString() is useful, but breaks down once any item has 10+ siblings (the basic structure is /...
- 11.9k
14
votes
6
answers
12k
views
Converting flat structure to hierarchical
I need to create function which will be able to convert flat object to recursive object. Here is my example:
I have flat array:
var flatArray = [
{
Description: "G",
guid: "...
- 1,440
14
votes
3
answers
18k
views
How to self JOIN recursively in SQL?
I have a table:
Series
========
ID
SeriesName
ParentSeriesID
A series can be a "root" series, (ParentSeriesID is 0 or null) or it can have a Parent. A series can also be several levels down, i.e. ...
- 25k
13
votes
3
answers
29k
views
PostgreSQL recursive parent/child query
I'm having some trouble working out the PostgreSQL documentation for recursive queries, and wonder if anyone might be able to offer a suggestion for the following.
Here's the data:
...
- 2,500
13
votes
1
answer
33k
views
sql select parent child recursive in one field
I do not know how to select query recursive..
id idparent jobNO
--------------------------------
1 0 1
2 1 2
3 1 3
4 0 ...
- 133
13
votes
4
answers
32k
views
SQL Query for Parent Child Relationship
I have db table with parent child relationship as:
NodeId NodeName ParentId
------------------------------
1 Node1 0
2 Node2 0
3 Node3 1
4 ...
- 503
13
votes
2
answers
26k
views
Hierarchical queries in MySQL
I'm trying to find all the parents, grandparents, etc. of a particular field with any depth. For example, given the below structure, if I provide 5, the values returned should be 1, 2, 3 and 4.
| a |...
user1035084