Hot answers tagged

6

This is easily achievable with an in-line subquery : select m.id, (select count(*) from messages where parent_id= m.id ) as ChildCount from messages m where m.parent_id = 0 Note that no group by is needed because a sub-query is used.


6

A very primitive implementation: It basically divides the problem into two subproblems: First find all the ancestors of the node in question (including the node itself). If the node has no parents, then this would be just itself. Then find the descendants of all those ancestors (including themselves). We may have several nodes in the ancestors result set, ...


5

Use a composite foreign key: (tree_id, parent_id) REFERENCES (tree_id, id) You will need to first change the PK or add a UNIQUE constraint on (tree_id, id)


5

Some of this information like index depth can be found in DMF dm_db_index_physical_stats(). Some outer interesting information that can be found in it is number of used data pages and the fragmentation level. Personally I have used the following query to get the state of the indexes for current database SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind....


4

This function returns the parent level of node_id: There is a 'level' row due there isn't a row (id, null) for parent row. CREATE FUNCTION get_parent(node_id int) RETURNS integer AS $$ WITH RECURSIVE get_parent AS ( SELECT t1.id, t1.parent_id, t1.name, 0 AS level FROM tree t1 ...


4

The following query: SELECT m1.id, m1.coa, IFNULL(sum(t.debit), 0) as debit, IFNULL(sum(t.credit), 0) as credit FROM master m1 JOIN master m2 ON m2.coa like CONCAT(m1.coa, '%') LEFT JOIN transaction t ON m2.id = t.id GROUP BY m1.id; Returns the following for me: +----+------+-------+--------+ | id | coa | debit | credit | +----+---...


4

A recursive CTE (rCTE) is the right tool to follow the path and identify all nodes of a tree. But it does not allow aggregation in the recursive term. There is an elegant alternative: Recursive function CREATE OR REPLACE FUNCTION f_item_tree(_item_id int, _level int = 7) RETURNS jsonb LANGUAGE sql STABLE PARALLEL SAFE AS $func$ SELECT CASE WHEN _level &...


3

I would first rethink the design. You only need one table: CREATE TABLE c ( id integer PRIMARY KEY ,parent_id integer REFERENCES c(id) ,state boolean ); With the layout as presented in the question, the query could be: SELECT DISTINCT a.* FROM a JOIN b ON a.node_id = b.id LEFT JOIN a ca ON ca.child_node_id = a.node_id LEFT JOIN b cb ON cb.id = ...


3

This does the job: CREATE MATERIALIZED VIEW speedy_materialized_view AS WITH RECURSIVE tree AS ( SELECT id, parent_id, ARRAY[id] AS path FROM gear_category WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, path || c.id FROM tree t JOIN gear_category c ON c.parent_id = t.id ) , tree_ct AS ( SELECT t.id, t.path, ...


3

The execution plan of your adjacency list example shows that it is able to use the primary key index (RDB$PRIMARY108) for both parts of the UNION. This is very efficient, mainly because you start with a single value, and then recursively look up its descendents (which is probably just a small set of the entire set of records). For the nested set execution ...


3

So the three questions I'm hoping someone can answer are: 1.Should I be using a CURSOR here or is there an alternative that will get the same recursive result? No, you should not. The CURSOR keep being opened over and over. Thinking of the overhead make me cringe. Personally, I stay away from CURSORs. 2.How can I get the results back in a single result ...


3

You should use LEFT JOIN instead of INNER JOIN in case category doesn't have subcategory. SELECT `parent`.`id` , `parent`.`name` , COUNT(`child`.`id`) AS `hasChild` FROM `category` `parent` LEFT JOIN `category` `child` ON `parent`.`id` = `child`.`parent_id` WHERE `parent`.`parent_id` = 3780 GROUP BY `...


3

This query will give you the desired output: SELECT parent.id , COUNT(child.id) AS child_count FROM messages parent INNER JOIN messages child ON child.parent_id = parent.id WHERE parent.parent_id = 0 GROUP BY parent.id; You can see this code in action here on SQL Fiddle. I have used a join in my solution, whereas druzin used a ...


3

Im not sure on why you want to track the make up of the stains but realistically, you can keep your original design because as stated in your question: Take a look at the most complicated lineage I gave up above: StrainY = {(Strain1 x Strain1) x (Strain2 x Strain2)} x {(Strain1 x Strain1) x (Strain670 x Strain670)} {(Strain1 x Strain1) x (Strain2 x ...


2

Bill Karwin covers the alternatives well in his presentation. I have a few notes on a similar but slightly different closure table on this page. The idea is to have one table that represents the parent relation, and another that represents the ancestor relation. All changes to the ancestor relation is a consequence of a change in the parents relation. As an ...


2

I would create a separate table for history. If it was me, I would use an auto-increment column for the primary key (historyID) , a column for the vial ID, and a column for a location ID. I would think the vial ID would be foreign key to the nodes table. You could then query against a vial to see it's full history. Since you don't list what your lookup ...


2

In this case I think you don't need a recursive query, simply get those records that do not have two child records. select * from mytable where id not in(select parent_id from mytable group by parent_id having count(*) > 1); id | parent_id -: | --------: 4 | 2 5 | 2 6 | ...


2

Inner join returns all ancestors of an specific path, I've used array_agg(distinc to avoid duplicates. select array_agg(distinct p2.name) permissions from permissions p1 join permissions p2 on p2.path @> p1.path Then I've used it for each role that match the path company_employee....


2

The design should be according to your requirements. If your requirement includes to store only some columnar data that are mostly required (mostly not null) then use tables with relations (foreign keys). If you just need some logging with situational tree structure data then use JSONB. For example, suppose that you store io_counters when counters are ...


2

I've thought of several possible solutions, but they all leave something to be desired. For example, I could add an owner_id column to the folders table, referencing users (id). The negative is that the owner would have to be repeated on every row, and ensuring that the entire tree has the same owner would be... difficult. (Maybe a foreign key on (parent_id, ...


2

From a very basic overview, I don't think your existing design is bad. In general, it's good to start out following a somewhat normalized pattern with your table design. Usually, I'll try to keep my tables thin enough to qualify the basic object they represent, usually grouping highly related fields within the same tables. If there are less used, less ...


2

I'm not entirely clear from your question whether you primarily want to just limit depth traversal, or if in reality you would actually want a solution for recursion loops if it was possible. I've gone for the latter. What you can do is add an extra column to the rCTE, which concatenates the ascendants together. Then you check in the recursive join for the ...


2

It's pretty simple, really: you add the recursion level value and filter on it: WITH RECURSIVE t(item_id, json, level) AS ( SELECT item_id, to_jsonb(items), 1 FROM items WHERE NOT EXISTS ( SELECT 1 FROM joins WHERE items.item_id = joins.item_id ) UNION ALL SELECT ...


1

Every XML database will have its own solutions to these problems. You could do worse that explore the source code of an open-source XML database. The main aspects of any solution will be (a) the ability to navigate from a node to its children or parent by following some kind of pointer structure rather than by parsing raw XML and searching sequentially, (b) ...


1

After much googling, I came to realize that the error message was exactly correct though somewhat vague. Truncated incorrect DOUBLE value: '1\x00\x00\x00' meant that somewhere in my query, my CAST BINARY was being converted back to a DOUBLE data type. Once I figured that out, re-reading the query made me consider how this little gem was working: cte....


1

I found the solution. I was using the recursive expression how parameter to join and it was do several lops on the table used on join, a better aprouch is before join with this table(sadt), do the join with recursive expression(downlots "table") and after, using result, do join with the sadt, with that, the query jump from 8sec to 8ms. Follow the solution: ...


1

Here is an example query, WITH RECURSIVE t(item_id, json) AS ( SELECT item_id, to_jsonb(items) FROM items WHERE NOT EXISTS ( SELECT 1 FROM joins WHERE items.item_id = joins.item_id ) UNION ALL SELECT parent.item_id, to_jsonb(parent) || jsonb_build_object( '...


1

A possibility is to split the folder table in two, treating the root folders as they were special case of folders (inheritance): CREATE TABLE users ( id SERIAL PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE folders ( id SERIAL PRIMARY KEY, parent_id INTEGER REFERENCES folders (id), name TEXT NOT NULL ); CREATE TABLE root_folders ( ...


1

In the original description of a B-tree the internal nodes held not just the index keys but all columns of the table. In the B+-tree only the leaf nodes store all columns' values. As each node in the tree is a fixed size, removing non-key data from internal nodes frees space for more keys to be held. All operators can be made to work with the BTree/ B+Tree ...


1

There are many implementation options available to developers of BTree and B+Tree algorithms that will affect the answer here. In a simplistic BTree, all nodes are the same size, and when a node overflows it is split into two half-full nodes with no other key redistribution occurring. Since there will on average be a uniform distribution nodes between half-...


Only top voted, non community-wiki answers of a minimum length are eligible