All Questions
16 questions
1
vote
2
answers
46
views
How to convert a recursive query to a function?
I have written a PostgreSQL query that's working perfectly:
WITH RECURSIVE x AS (
SELECT i, parent, id, name, type, '' AS path
FROM entry
WHERE name = 'JS-VBNET-2'
UNION ALL
SELECT e.i,...
0
votes
1
answer
16
views
How to fuzzy query a directory structure in PostgreSQL?
I was able to put together a rough idea for a PostgreSQL query to query over a node table, which contains id, parent__id, slug, and file_url (optional). It's considered a file if it has file_url, ...
- 221
-2
votes
1
answer
211
views
I want to make a tree of parents only in sql server
My table in the database
How can I get my result as Where child = 'chl1'?
1
vote
0
answers
62
views
TSQL - retrieve all IDs in a tree for a given subnode
I've tried to translate and implement the PostGres solution to the problem found here: PostgreSQL - retrieve all IDs in a tree for a given subnode, but I'm either programming it wrong, or ...
- 111
5
votes
1
answer
2k
views
How to turn a set of flat trees into a single tree with multiple leaves?
We have this beautiful Postgres tree generator. Yet it kind of produces cuts of a tree not a whole tree all at once:
item_id jsonb_pretty
1 {
"title": "PARENT",
"...
- 75
0
votes
2
answers
632
views
How to limit tree traversal in case of loops?
So I want to limit tree depth traversal (as the simplest recursion killer tool). Example code that turns normalized records into tree:
CREATE TABLE items (
item_id serial PRIMARY KEY,
title ...
- 75
3
votes
1
answer
5k
views
Recursively find the path to all leaves descending from a given parent in a tree
I'm trying to write a query to identify the path to each furthest descendant of a particular parent node in a table of tree structures like:
0 1
| |
2 3
| |
4 5
...
- 155
2
votes
0
answers
483
views
Drop intermediate results of recursive query in Postgres
I'm trying to aggregate the contents of a column from a directed acyclic graph (DAG) stored in a Postgres table.
Each dag row has an id, bytes, and may also have a parent referencing another row. I'm ...
- 155
0
votes
1
answer
2k
views
Calculate products count based on category tree table
It is necessary to calculate how many products were created in each category, of course, that the products created in a child must be considered for the parent.
How can i do that SQL/Postgresql?
...
- 3
0
votes
1
answer
638
views
Exclusion constraint on recursive foreign key (on the example of a Tree)
I have the table with recursive foreign key. It's just hierarchical tree structure:
CREATE TABLE tree (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
value ...
- 111
3
votes
2
answers
1k
views
Joining two ltree based tree tables recursively
I have two tables, roles and permissions, both of which use ltree for maintaining a tree structure. I also have a pivot table, roles_permissions, which serves to connect the two tables. How can I join ...
- 131
9
votes
2
answers
7k
views
PostgreSQL - retrieve all IDs in a tree for a given subnode
I have a non-binary tree of customer, and I need to obtain all the IDs in a tree for the given node.
The table is very simple, just an join table with a parent id and child id.
This is a ...
- 123
5
votes
2
answers
8k
views
Create JSON object from recursive tree structure
Having this simple many-to-many self-referential structure.
An item owns other items through the joins table:
CREATE TABLE items (
item_id serial PRIMARY KEY
, title text
);
CREATE TABLE ...
- 179
0
votes
1
answer
274
views
Binary tree "get open list" SQL query
I have a node table that looks like this, plus it has an inserted_at column:
id | parent_id
----------+-----------
1 | nil
2 | 1
3 | 1
4 | 2
5 | 2
...
- 103
19
votes
1
answer
14k
views
How do I sort the results of a recursive query in an expanded tree-like fashion?
Let's assume you have a nodes tables like this:
CREATE TABLE nodes (
node serial PRIMARY KEY,
parent integer NULL REFERENCES nodes(node),
ts timestamp NOT NULL DEFAULT now()
);
It ...
- 525
2
votes
1
answer
14k
views
Recursive Query in MySQL using stored proceedure and CURSOR
I'm expanding our user/group system to allow for dynamic groups which are made up of other groups members. I have three tables users, groups, and relationships.
For simplicity sake, lets say users ...
- 506