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 | +----+---...


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

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 ...


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

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 ...


2

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 ...


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

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

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

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, ...


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

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

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-...


1

I believe you are asking about a table design or schema. Whether you use 1 or many tables is up to you. The increased complexity of normalisation is a trade off between efficiency and convenience. The easiest option to begin with is to use a single table to describe the car, and create a field for each piece of information you want to store. Start with ...


1

I'm a DB noob as well, I do recall postresql having json datatype for storing JSON structures. Maybe you can review the postgresql doc and decide if it works for you.


1

How indexes work in InnoDB. This discussion is limited to InnoDB. (Let me establish some ground work, then I will get to your question.) The PRIMARY KEY is a BTree where the leaf nodes contain all the columns of the row. That is, the PK and the data coexist in the same BTree. Each secondary key is a BTree where the leaf nodes contain a copy of the ...


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

I agree with Hector's assessment. It will probably be more flexible just to keep your original design. The piece that you might have missed to make it work is using recursion to traverse up the tree. I don't know much about recursion in MySQL, but this answer on StackOverflow feels like a good resource. The biggest problem with your proposed alternate is ...


1

You can't express the constraint that an edge-list must be acyclic as a CHECK constraint. It would have to be an ASSERTION constraint, which PostgreSQL does not support. You'll have to LOCK TABLE to prevent concurrent INSERTs or UPDATEs and check it with an AFTER trigger. The resulting lock upgrade issues will result in frequent deadlocks. To prevent ...


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