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

You could formalize the approach that you're already using and make it more efficient to both maintain and query. Consider an ERD like so: Note that all of your hierarchy is flattened into a single set of columns. There is an involuted relationship so that each child entity in the hierarchy can point to it's immediate parent. I've also included left and ...


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


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

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

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

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


2

Although MySQL has no CTE functionality, there are two major ways to create CTE expressions: TECHNIQUE #1 : Write Stored Procedures to Traverse Recursively Rather than Reinventing the Wheel, please see my past posts on how to make stored procedures Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs Oct 26, 2012 : Hierarchical ...


2

If you really have to modify these data rarely, then you can simply store the result of the CTE in a table, and run queries against this table. You can define indexes based on your typical queries. Then TRUNCATE and repopulate (and ANALYZE) as necessary. On the other hand, if you can put the CTE in separate stored procedures rather than a view, you can ...


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

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

You don't say what DBMS you're using, so it is possible yours does not. The majors, however, will maintain the table and indexes as completely consistent at all times. As the very first row is written the storage engine will (by-and-large) amend all indexes on that table to match. When the second row is written those indexes will be amended once again. If ...


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

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

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


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