Stack Overflow
Stack Overflow

Questions tagged [materialized-path-pattern]

46 questions
19
votes
3 answers

Is it possible to make MySQL use an index for the ORDER by 1 DESC, 2 ASC?

I have a materialized path-driven bulletin board. It is using the following query to get the messages in order, SELECT * FROM Board ORDER by root DESC, path ASC LIMIT 0,100 where root is an id of the root message for the thread, and path is a…
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
17
votes
5 answers

Sorting tree with a materialized path?

I have a tree structure in a table and it uses materialized paths to allow me to find children quickly. However, I also need to sort the results depth-first, as one would expect with threaded forum replies. id | parent_id | matpath | …
Ovid
  • 11,580
  • 9
  • 46
  • 76
13
votes
1 answer

PostgreSQL ltree- vs tree module vs integer/string arrays or string delimited path

As you may know there's a module for PostgreSQL called ltree. Also you have the possibility to use the Array type for integers (*1, see comment below), which in this test shows to actually perform a little slower with its recursive queries, compared…
Dac0d3r
  • 2,176
  • 6
  • 40
  • 76
12
votes
1 answer

Build a JSON tree from materialized paths

I'm planning on using materialized paths in MongoDB to represent a tree and need to convert the materialized paths back into a JSON tree. ex. // Materialized path var input = [ {"id": "0", "path": "javascript" }, {"id": "1", "path":…
nevf
  • 4,596
  • 6
  • 31
  • 32
12
votes
2 answers

Materialized path pattern VS Hierarchyid

I am reading the SQL server 2008 bible and it says the materialized path pattern is significantly faster then the hierarchyid. Is this really true? How can I make the hierarchyid have equal or better performance.
Luke101
  • 63,072
  • 85
  • 231
  • 359
9
votes
2 answers

PostgreSQL Materialized Path / Ltree to hierarchical JSON-object

I have this materialized path tree structure built using PostgreSQL's ltree module. id1 id1.id2 id1.id2.id3 id1.id2.id5 id1.id2.id3.id4 ... etc I can of course easily use ltree to get all nodes from the entire tree or from a specific…
Dac0d3r
  • 2,176
  • 6
  • 40
  • 76
6
votes
1 answer

TypeORM does not update MaterializedPath

I am trying to update materialized path in SQLite3 database with usage of TypeORM. The table for folders (which I am updating) has both parent and parentId columns. When I update parent column, the parentId column is going to update, but not the…
Jiri Kralovec
  • 1,487
  • 1
  • 10
  • 18
5
votes
1 answer

Selecting based on path in mysql

I have a column id, a column parent and a column path that is a materialized path. It looks like 1 | \N | 1 2 | 1 | 1/2 3 | 2 | 1/2/3 4 | 3 | 1/2/3/4 5 | 3 | 1/2/3/5 6 | 2 | 1/2/6 7 | 6 | 1/2/6/7 8 | 2 | 1/2/8 9 | 1 …
Hailwood
  • 89,623
  • 107
  • 270
  • 423
4
votes
1 answer

Build tree from materialized path

I have a trouble building a tree structure from materialized path using ruby. Assuming I have a sorted result set (from couchdb): [ { :key => [], :value => "Home" }, { :key => ["about"], :value => "About" }, { :key => ["services"], :value =>…
Sammy
  • 229
  • 2
  • 8
4
votes
4 answers

Searching for the right-most node of a materialized path tree

Is it possible to sort by a materialized path tree's path text field in order to find the right-most node of the tree? For example, consider this python function that uses django-treebeard's MP_Node: def get_rightmost_node(): """Returns the…
nnyby
  • 4,748
  • 10
  • 49
  • 105
4
votes
1 answer

Select only direct descendants from table with a materialized path

I'm trying to use the 'Materialized Path' pattern for storing a directory structure in a SQLite database on an Android app. My single table has a primary key field that is the file path, like…
elprl
  • 1,940
  • 25
  • 36
3
votes
1 answer

The best way to generate path pattern for materialized path tree structures

Browsing through examples all over the web, I can see that people generate the path using something like "parent_id.node_id". Examples:- uid | name | tree_id -------------------- 1 | Ali | 1. 2 | Abu | 2. 3 | Ita | 1.3. 4 | Ira | …
k4ml
  • 1,196
  • 12
  • 17
3
votes
0 answers

Materialized path tree structure with support for duplicate path ids in MongoDB?

In the guide for materialized path tree structures at mongodb docs they show a simple example of how to structure data to support simple querying: db.categories.insert( { _id: "Books", path: null } ) db.categories.insert( { _id: "Programming", path:…
Mickel
  • 6,658
  • 5
  • 42
  • 59
3
votes
1 answer

Riak store a tree structure

I'm developing a distributed file system with riak for a project.I want to store a tree structure in the riak database and , I want to get the node id When the path is given(A Path like /root/dev/bin) I thought to use materialized paths to store the…
Sameera Kumarasingha
  • 2,908
  • 3
  • 25
  • 41
2
votes
1 answer

Efficient query to get n-depth descendants of a node with materialized path pattern in Mongodb

Let's say we have a tree structure like: node 0 node 0.0 node 0.1 node 0.1.0 node 0.1.1 node 0.1.1.0 ... Each node stores it's path like 0.1.1.0 where digit is an id field(it can be mongodb ObjectId for example) and . is a separator(it…
Tamal Govinda das
  • 575
  • 5
  • 11
1
2 3 4