Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Nov 2009
    Posts
    53
    Thanks
    3
    Thanked 0 Times in 0 Posts

    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
    New Coder
    Join Date
    Nov 2009
    Posts
    53
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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

  • #3
    New Coder
    Join Date
    Aug 2011
    Location
    Sydney
    Posts
    26
    Thanks
    2
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by bonecone View Post
    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
    Umm where are you testing the value of the children field? In your actual code?
    “Don't pray for lighter burdens, but for stronger backs.”
    Deals in Sydney

  • #4
    New to the CF scene
    Join Date
    Sep 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I worked something like this recently, I think you just helped me find a solution. Thank you in advance




  •  

    Posting Permissions

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