Recent

Author Topic: Count number of elements in root groups via single query?  (Read 391 times)

Mr.Madguy

  • Sr. Member
  • ****
  • Posts: 455
Count number of elements in root groups via single query?
« on: September 16, 2019, 09:47:06 am »
I have MySQL 5.5 database. It has structure like this:
IDPARENT_IDNAMEEL_TYPE
10Some root groupGroup
21Second level groupGroup
32Some elementElement
What I need - to count total number of elements in every root group. So, perfect result would look like this:
NAMECOUNT
Some root group1
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.
« Last Edit: September 16, 2019, 09:54:12 am by Mr.Madguy »
DynamicData 3.0 is released!
Since now development is frozen - only optimization passes will be made at some point.
Lack of multiple inheritance turns it into abomination.

Zvoni

  • Full Member
  • ***
  • Posts: 236
Re: Count number of elements in root groups via single query?
« Reply #1 on: September 16, 2019, 11:09:08 am »
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
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircrafts

marcov

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 7430
Re: Count number of elements in root groups via single query?
« Reply #2 on: September 16, 2019, 11:26:02 am »
(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)

Zvoni

  • Full Member
  • ***
  • Posts: 236
Re: Count number of elements in root groups via single query?
« Reply #3 on: September 16, 2019, 11:36:34 am »
(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*
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircrafts

Abelisto

  • Jr. Member
  • **
  • Posts: 81
Re: Count number of elements in root groups via single query?
« Reply #4 on: September 16, 2019, 11:50:58 am »
Usually it solved by recursive CTE, but MySQL introduced this feature only in 8.0 version.
http://www.mysqltutorial.org/mysql-recursive-cte/
OS: Linux Mint + MATE, Compiler: FPC trunk (yes, I am risky!), IDE: Lazarus trunk

Martin_fr

  • Administrator
  • Hero Member
  • *
  • Posts: 5621
    • wiki
Re: Count number of elements in root groups via single query?
« Reply #5 on: September 16, 2019, 12:08:38 pm »
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.

valdir.marcos

  • Hero Member
  • *****
  • Posts: 821
Re: Count number of elements in root groups via single query?
« Reply #6 on: September 16, 2019, 05:55:31 pm »
I have MySQL 5.5 database. It has structure like this:
IDPARENT_IDNAMEEL_TYPE
10Some root groupGroup
21Second level groupGroup
32Some elementElement
What I need - to count total number of elements in every root group. So, perfect result would look like this:
NAMECOUNT
Some root group1
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

Mr.Madguy

  • Sr. Member
  • ****
  • Posts: 455
Re: Count number of elements in root groups via single query?
« Reply #7 on: September 16, 2019, 09:12:46 pm »
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.
DynamicData 3.0 is released!
Since now development is frozen - only optimization passes will be made at some point.
Lack of multiple inheritance turns it into abomination.