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
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 test_table WHERE parent_id=t.id) AS children FROM test_table AS t WHERE parent_id=$parent
But then I get the error message
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
#1054 - Unknown column 't.id' in 'where clause'