www.webdeveloper.com
Results 1 to 2 of 2

Thread: counting children in an adjacency list

  1. #1
    Join Date
    Oct 2009
    Posts
    68

    Question counting children in an adjacency list

    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

    Code:
    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

    Code:
    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'

  2. #2
    Join Date
    Oct 2009
    Posts
    68
    Found a solution afterwords

    Code:
    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles