From: Peter Brawley Date: September 27 2006 3:20pm Subject: Re: AW: Count of children List-Archive: http://lists.mysql.com/mysql/202141 Message-Id: <451A96A7.8080208@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-451A96A70FDD=======" --=======AVGMAIL-451A96A70FDD======= Content-Type: multipart/alternative; boundary=------------000909040502050906060803 --------------000909040502050906060803 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit André With an edge list, the solution entails recursion, so you need either an sproc or application proc. With a nested sets model, the count is dead simple. If the id of the target row is N, and the left & right node columns are named leftedge and rightedge, the query is SELECT COUNT(t2.id) FROM tbl t1 JOIN tbl t2 ON t2.leftedge > t1.leftedge AND t2.leftedge < t1.rightedge WHERE t1.id=N; PB ----- André Hänsel wrote: > I will use any model that is suitable. ;) > > I am somewhat familiar with both tree models but I can't come up with a > method to get the count of all sub- and sub-sub-nodes in either of them. > > >> -----Ursprüngliche Nachricht----- >> Von: Peter Brawley [mailto:peter.brawley@stripped] >> Gesendet: Mittwoch, 27. September 2006 16:49 >> An: André Hänsel >> Cc: mysql@stripped >> Betreff: Re: Count of children >> >> André, >> >> >>> I want the count of all sub-entries for a specific entry. >>> >> Depends on the model you are using--edge list or nested sets? >> >> PB >> >> ----- >> >> André Hänsel wrote: >> >>> I have a table with id and parent_id. >>> I want the count of all sub-entries for a specific entry. >>> >>> I found several documents about working with graphs/trees >>> >> in MySQL but I >> >>> could not find a solution for my problem. >>> >>> I can imagine two possibilities, but one is memory >>> >> intensive and the other >> >>> one creates load on updates. >>> The first is, that I select all entries and then use a >>> >> procedural language >> >>> to determine recursively whether an node is a sub-node of >>> >> the specific node. >> >>> The second is, that I store the sub-node count with each >>> >> node and when I do >> >>> an insert, I walk the tree upwards and increment the node-counts. >>> >>> Is there a smart solution/best practice for my problem? >>> >>> Now I can't think of another sentence starting with an i. ;-) >>> >>> Best regards, >>> André >>> >>> >>> >>> >> -- >> No virus found in this outgoing message. >> Checked by AVG Free Edition. >> Version: 7.1.407 / Virus Database: 268.12.9/457 - Release >> Date: 9/26/2006 >> >> > > > > --------------000909040502050906060803 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit André

With an edge list, the solution entails recursion, so you need either an sproc or application proc. With a nested sets model, the count is dead simple. If the id of the target row is N, and the left & right node columns are named leftedge and rightedge, the query is

SELECT COUNT(t2.id)
FROM tbl t1
JOIN tbl t2 ON t2.leftedge > t1.leftedge AND t2.leftedge < t1.rightedge
WHERE t1.id=N;

PB

-----

André Hänsel wrote: 
I will use any model that is suitable. ;)

I am somewhat familiar with both tree models but I can't come up with a
method to get the count of all sub- and sub-sub-nodes in either of them.

  
-----Ursprüngliche Nachricht-----
Von: Peter Brawley [mailto:peter.brawley@stripped] 
Gesendet: Mittwoch, 27. September 2006 16:49
An: André Hänsel
Cc: mysql@stripped
Betreff: Re: Count of children

André,

    
I want the count of all sub-entries for a specific entry.
      
Depends on the model you are using--edge list or nested sets?

PB

-----

André Hänsel wrote:
    
I have a table with id and parent_id.
I want the count of all sub-entries for a specific entry.

I found several documents about working with graphs/trees 
      
in MySQL but I
    
could not find a solution for my problem.

I can imagine two possibilities, but one is memory 
      
intensive and the other
    
one creates load on updates.
The first is, that I select all entries and then use a 
      
procedural language
    
to determine recursively whether an node is a sub-node of 
      
the specific node.
    
The second is, that I store the sub-node count with each 
      
node and when I do
    
an insert, I walk the tree upwards and increment the node-counts.

Is there a smart solution/best practice for my problem?

Now I can't think of another sentence starting with an i. ;-)

Best regards,
André


  
      
-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.407 / Virus Database: 268.12.9/457 - Release 
Date: 9/26/2006
--------------000909040502050906060803-- --=======AVGMAIL-451A96A70FDD======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.407 / Virus Database: 268.12.9/457 - Release Date: 9/26/2006 --=======AVGMAIL-451A96A70FDD=======--