Lazarus
Programming => Databases => Topic started by: Mr.Madguy on September 16, 2019, 09:47:06 am
-
I have MySQL 5.5 database. It has structure like this:
| ID | PARENT_ID | NAME | EL_TYPE |
| 1 | 0 | Some root group | Group |
| 2 | 1 | Second level group | Group |
| 3 | 2 | Some element | Element |
What I need - to count total number of elements in every root group. So, perfect result would look like this:
| NAME | COUNT |
| Some root group | 1 |
So, in fact, I need to go from element to root group or from root groups to elements recursively. I know, how to do it via Pascal code. But may be there is some easy way to do it via some single SQL query? Via using WHILE for example.
-
Going by the sparse data you've given us, i take it that a root group has ParentID=0 (as in: No Parent)
Why not just
SELECT Name, Count(*) FROM MyTable WHERE ParentID=0 GROUP BY Name
-
(I think this is a tree like structure mapped on the tabel. The example 1,2,3 all belong to the root group since they reference eachother 3->2->1->x)
-
(I think this is a tree like structure mapped on the tabel. The example 1,2,3 all belong to the root group since they reference eachother 3->2->1->x)
Crap. And he needs the count of elements, in his example that would be ID=3 *sigh*
-
Usually it solved by recursive CTE, but MySQL introduced this feature only in 8.0 version.
http://www.mysqltutorial.org/mysql-recursive-cte/ (http://www.mysqltutorial.org/mysql-recursive-cte/)
-
If you can change the way you store the data: https://en.wikipedia.org/wiki/Nested_set_model
However, this means more work for each insert, as for each insert many rows need to be updated.
-
I have MySQL 5.5 database. It has structure like this:
| ID | PARENT_ID | NAME | EL_TYPE |
| 1 | 0 | Some root group | Group |
| 2 | 1 | Second level group | Group |
| 3 | 2 | Some element | Element |
What I need - to count total number of elements in every root group. So, perfect result would look like this:
| NAME | COUNT |
| Some root group | 1 |
So, in fact, I need to go from element to root group or from root groups to elements recursively. I know, how to do it via Pascal code. But may be there is some easy way to do it via some single SQL query? Via using WHILE for example.
Although not always the best peformance option, using database stored procedure is generally easy for programmers.
CTE from MySQL 8+ would be your best solution.
Some extra information that might help you:
https://stackoverflow.com/questions/8869402/sql-recursive-select-all-childs-from-a-given-node-in-a-tree
https://stackoverflow.com/questions/1323245/sql-recursion-without-recursion
https://stackoverflow.com/questions/5291054/generating-depth-based-tree-from-hierarchical-data-in-mysql-no-ctes/5291159#5291159
https://riptutorial.com/sql/example/11142/recursive-joins
https://www2.cs.duke.edu/courses/spring04/cps216/lectures/rec-02-recursion.pdf
https://dnhome.wordpress.com/2014/04/04/sql-recursive-select-parent-child/
https://medium.com/@CodyEngel/recursive-queries-in-sql-95af9f9fd89c
-
As I can't change anything in this DB, I had to implement everything in Pascal. Yeah, I don't like this solution, as it puts more stress on DB, as it requires much more queries and isn't atomic, as I have to store some data in temporary objects, that can possible lose sync with DB, while I read some other data. (But I'm not sure about it. May be Transaction object can make it atomic.) But I have no choice.