query to get total count in a hierarchy

asa_carter

Can I use a MySQL query to get a total count of items stored in a hierarchy. Eg:

id parentId
1 0
2 1
3 1
4 2

I can do it with a recursive function in php but thought this might be quicker and easier as I have to get a few different counts for different things.

sfullman

hope i am not misinterpreting your request

based on your post, some queries you could use:

select parentid, count(*) AS childcount FROM thattable GROUP BY parentid ASC;

also, you can do a left join to get ALL parent ids by joining the parent table:

select a.ID, COUNT(DISTINCT b.parentid) AS childcount FROM parenttable a LEFT JOIN thattable b ON a.ID=b.parentid GROUP BY a.ID;

the last query adds the ability to sort in a meaningful way:

select a.ID, COUNT(DISTINCT b.parentid) AS childcount FROM parenttable a LEFT JOIN thattable b ON a.ID=b.parentid GROUP BY a.ID ORDER BY a.lastname, a.firstname; [assuming those fields are present]

on the previous query, childcount is going to be zero if that parent id is not found in the child table, great for summaries of ALL parent items

P.S. Some more knowledgable sql guys might have something to say about that COUNT(DISTINCT b.parentid) as not the best way - I'm vaguely thinking there might be a better that that does work 🙂

Cheers,
Samuel

asa_carter

I've been doing some googling and found that is not possible to do some of things I wanted to do in pure SQL.

"For a single path in SQL we would have to know the depth of the node we need a path from. We can use procedural code to do the same thing without knowing the depth."

Thanks anyway 🙂

Weedpacket

asa_carter wrote:
I've been doing some googling and found that is not possible to do some of things I wanted to do in pure SQL.

Depends on how you store the data. With some methods for storing hierarchical data the query to obtain the depth of a subtree is fairly trivial.

There are also things called stored procedures for writing procedural code in SQL. Then the query could be just "select tree_depth(id) where id=$node_id"

asa_carter

Sorry yeah I meant using the the adjacency list model (which I am using) rather than the the nested set model.

I am creating a discussion board.

I need to get things like:

the last post in a thread.
total replies to any post
number of unique authors in a thread
.. and probably a few more.

I've got most of these working in a php function but I'm having problems getting the number of distinct authors. I keep getting a mysql error:

$authors contains: Asa Carter,Asa Carter

echo $numAuthors = mysql_num_rows(mysql_query("SELECT DISTINCT author FROM discussion WHERE author IN ($authors)"));

asa_carter

Ah never mind, I dont even need to do a query :queasy:

echo count(array_unique($postsDetails[2]));

sfullman

on a 2-column table like that, you're well indexed, suggest you write a recursive function which returns the array you need and does calculations as far as who each thread belongs to along the way (and share it). your calculation time would be small even it it's not glamorous.

cheers,
Samuel