Recent

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

Mr.Madguy

  • Hero Member
  • *****
  • Posts: 844
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 »
Is it healthy for project not to have regular stable releases?
Just for fun: Code::Blocks, GCC 13 and DOS - is it possible?

Zvoni

  • Hero Member
  • *****
  • Posts: 2315
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 Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

marcov

  • Administrator
  • Hero Member
  • *
  • Posts: 11382
  • FPC developer.
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

  • Hero Member
  • *****
  • Posts: 2315
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 Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Abelisto

  • Jr. Member
  • **
  • Posts: 91
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: 9791
  • Debugger - SynEdit - and more
    • 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: 1106
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

  • Hero Member
  • *****
  • Posts: 844
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.
Is it healthy for project not to have regular stable releases?
Just for fun: Code::Blocks, GCC 13 and DOS - is it possible?

 

TinyPortal © 2005-2018