...

View Full Version : counting children in an adjacency list



bonecone
06-09-2011, 12:50 PM
I've created an adjacency list to store a directory tree. To make it run faster I only load one level of the tree at a time. When you click on a plus button next to one of the categories, ajax retrieves the children for that category and displays them.

When the tree is being rendered, the program needs to know whether to add a plus button next to a category or not. So it has to count the number of children for each category to see if it's greater than zero.

I've used this query



SELECT t.*, (SELECT COUNT(id) FROM test_table WHERE parent_id=t.id) AS children FROM test_table AS t WHERE parent_id=$parent


It works, but I realized I don't need to count all the children of a category, I just need to confirm that there's at least one child. So to make things run faster I tried this instead



SELECT t.*, (SELECT COUNT(id) FROM (SELECT id FROM test_table WHERE parent_id=t.id LIMIT 1) AS limit_children) AS children FROM test_table AS t WHERE parent_id=$parent


But then I get the error message
#1054 - Unknown column 't.id' in 'where clause'

bonecone
06-09-2011, 02:33 PM
Found a solution afterwords


SELECT t.*, (SELECT COUNT(id) FROM test_table WHERE parent_id=t.id LIMIT 1) AS children FROM test_table AS t WHERE parent_id=$parent

And just test of the chldren field is NULL

kunz
09-02-2011, 01:54 PM
Found a solution afterwords


SELECT t.*, (SELECT COUNT(id) FROM test_table WHERE parent_id=t.id LIMIT 1) AS children FROM test_table AS t WHERE parent_id=$parent

And just test of the chldren field is NULL

Umm where are you testing the value of the children field? In your actual code?

janice14
09-19-2011, 05:22 AM
I worked something like this recently, I think you just helped me find a solution. Thank you in advance


(http://eblogz.net)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum