counting children in an adjacency list

[bonecone]

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]

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