Professional Web Applications Themes

age old recursion problem.. - Coldfusion - Advanced Techniques

I have gone thru the related recursion topics, however I can not use SQL based solutions since the db structure cannot be changed. so here is my problem: Table GROUPS has fields (id,parentid,name) And I have to display it in hierarchical order: group1 group11 group12 group121 group22 group2 etc... u get what I mean... Can anyone plz refer me to a solution? I have been breaking my head over this for more than a couple of hours but nouse. I tried to use cffunction, have a main query that is cached and the recursive function hits this cached query for ...

  1. #1

    Default age old recursion problem..

    I have gone thru the related recursion topics, however I can not use SQL based
    solutions since the db structure cannot be changed.
    so here is my problem:
    Table GROUPS has fields (id,parentid,name)
    And I have to display it in hierarchical order:

    group1
    group11
    group12
    group121
    group22
    group2

    etc... u get what I mean... Can anyone plz refer me to a solution? I have been
    breaking my head over this for more than a couple of hours but nouse. I tried
    to use cffunction, have a main query that is cached and the recursive function
    hits this cached query for its subqueries. But something to do with the
    variable scopes I think, it totally failes to bring out the solution Can anyone
    plz clear this once for all?

    regards,
    Teja


    STeja Guest

  2. #2

    Default Re: age old recursion problem..

    It'll be slow, but it might work.

    Step 1. run a query to get your records. Use your database to produce a
    field in your select list that consists of a concatonation of the letter a and
    the id in question, cast as a string. Also include a number of dummycolumns at
    the end. Give them some string value, 'aaaa' is a good value. Your recordset
    should resemble

    id | concatonation | other fields | dummyfield1 | dummyfield2 etc
    1 | a1 | whatever | aaaa |
    aaaa
    11 | a11 | whatever | aaaa | aaaa
    5 | a5 | whatever | aaaa |
    aaaa
    22 | a22 | whatever | aaaa | aaaa
    etc. So far the sort order doesn't matter.

    Then start doing this sort of thing, only with proper syntax.
    <cfloop query="yourquery" index = "ii">
    <cfset x = QuerySetCell(yourquery, dummy1, mid(concatonation, 2, 1), ii>
    etc

    Then do a Q of Q ordered by dummy1, dummy2, etc

    Originally posted by: STeja
    I have gone thru the related recursion topics, however I can not use SQL based
    solutions since the db structure cannot be changed.
    so here is my problem:
    Table GROUPS has fields (id,parentid,name) -- no referential integrity on
    this table. id is the only (primary )key declared.
    And I have to display it in hierarchical order:
    group1
    ->group11
    ->group12
    group2
    ->group21
    ->->group211
    group3

    etc... ignore the crappy example above, I just made up those group names. but
    u get what I mean... Can anyone plz refer me to a solution? I have been
    breaking my head over this for more than a couple of hours but nouse. I tried
    to use cffunction, have a main query that is cached and the recursive function
    hits this cached query for its subqueries. But something to do with the
    variable scopes I think, it totally failes to bring out the solution Can anyone
    plz clear this once for all?

    regards,
    Teja




    Dan Guest

  3. #3

    Default Re: age old recursion problem..

    I would use a stored procedure to return this resultset. Your initial query in
    the stored proc should select the root nodes- i.e. the records with no parent.
    Then you can use a cursor to loop over the initial result set and return any
    children of each node. You will use recursion to get the children of the
    children, etc. until no records are returned for a particular child node.

    The principle is the same whether you do this using stored procs or CF, but I
    guarantee it will run a lot faster if you write it against SQL Server directly.
    Break out a SQL book or the online docs for stored proc examples using cursors
    and calling other stored procs.

    CFMunster Guest

  4. #4

    Default Re: age old recursion problem..

    Hi

    You could write a Cross Tab query which will gives the format which you want to return.
    i think it will works fine for your creiteria.
    vkunirs Guest

  5. #5

    Default Re: age old recursion problem..

    I've looked at this sort of thing before, and have found that the easiest way
    to achieve this is by using a recursive custom CF tag, like this;

    <cf_showgroups rootid="null"> (Assuming that the root group has ParentId =
    null)


    In showgroups.cfm I'd put;

    <cfquery name="GetGroups" datasource="MyDatasource">
    SELECT Id, Name FROM Groups WHERE ParentId = #attributes.rootid#
    </cfquery>

    <cfoutput query="GetGroups">
    #Name#
    <cf_showgroups id="#id#">
    </cfoutput>

    If you need to do indentation you can pass another attribute for 'level' and
    increment it within showgroups.cfm

    This is probably not a very efficient method if you're going beyond 5 or 6
    levels and a few hundred entries, but it works for me and it's fairly clean and
    managable. Just make sure that ParentId is indexed.

    slez Guest

  6. #6

    Default Re: age old recursion problem..

    Thanks a lot guys, the slez solution is the quickest. Though it would hog the
    system after a few levels, for now it works and I am happy.. Cann't believe I
    couldn't solve it. I got into coldfusion on an adhoc basis and haven't really
    sat down and read a book on it.. and I realize somethings work in a weird way
    all of a sudden.. Can anyone suggest a all in one book for coldfusion?? is
    BenForta's the only book??


    Teja

    STeja Guest

Similar Threads

  1. problem widt recursion fuction
    By jadrnicekCZ in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: July 28th, 03:41 PM
  2. Is Recursion Allowed?
    By DaveHCYJ in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: May 16th, 04:29 PM
  3. 256 levels of recursion??
    By floyduk in forum Macromedia Flash Actionscript
    Replies: 5
    Last Post: March 6th, 04:19 PM
  4. Size of dir (with recursion)
    By Jesper Noehr in forum PERL Beginners
    Replies: 4
    Last Post: December 29th, 11:08 PM
  5. Recursion
    By Jeff Westman in forum PERL Beginners
    Replies: 15
    Last Post: November 4th, 12:18 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139