EasyCFM.COM ColdFusion Forums / Application Beta Testing Area! / Recursive SQL CFC

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Recursive SQL CFC -- page: 1 2

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts first

louissto56
10-29-2008 @ 1:15 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1149
Joined: Jan 2007

I meant for the recursion cfc you have at the moment.
But id love to see what you come up with.

Louis

edit: just realised you posted the cfc Playful
thanks

-----------------------------
www.MyBizList.com.au - QLD Business Directory and rent-
a-site

This message was edited by louissto56 on 10-29-08 @ 1:17 AM

megan
10-28-2008 @ 10:07 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 2398
Joined: Jan 2003

ok - I'll post it here when I have a chance to figure something out Smile  megan

We have art so that we shall not die of reality ~ Nietzsche

louissto56
10-28-2008 @ 9:20 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1149
Joined: Jan 2007

Oh that sounds good. Can you send or link pweease.

Louis

-----------------------------
www.MyBizList.com.au - QLD Business Directory and rent-a-site

megan
10-27-2008 @ 9:25 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 2398
Joined: Jan 2003

Hi Louis - not sure why the extra spaces are showing up like that, just using ul and li to indent - basically the cfc I am using does not query the database like your cfc does - you feed it your query and then it formats the query result into a parent/child tree - I will have to play around with yours a bit and see if I can make it output a tree. - thanks, megan

We have art so that we shall not die of reality ~ Nietzsche

louissto56
10-27-2008 @ 9:03 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1149
Joined: Jan 2007

Not yet. Although I must say, I dislike your method of
nesting if I am interpreting it correctly. Is it really
nesting it such as in ul and li's or is it indenting it
with spaces. If its the latter than I disapprove :P

Louis

p.s. seems like chrome's textbox automatically inserts
line breaks rather than word-wrap just the textbox.
Lazy.

-----------------------------
www.MyBizList.com.au - QLD Business Directory and rent-a-site

megan
10-26-2008 @ 9:39 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 2398
Joined: Jan 2003

Oh - ok - I thought you had gone further and i was just wondering how you handled your different levels in output etc.  - thanks Smile  megan

We have art so that we shall not die of reality ~ Nietzsche

louissto56
10-26-2008 @ 1:12 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1149
Joined: Jan 2007

To be honest. I havent really expanded on it with that ability. I also came to realise my way of
doing what you suggested was with help from the group attribute in cfoutput which may cause
problems with more than 2 levels. I will have to work on it later when I have time.

Just felt like doing it to get back into the SQL and CF mind frame.

Louis

-----------------------------
www.MyBizList.com.au - QLD Business Directory and rent-a-site

megan
10-25-2008 @ 8:06 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 2398
Joined: Jan 2003

Hi Again Louis - I saw your dump - thanks :D - I guess I should have been more specific - how would you output (cfoutput) the query returned by the cfc so that the results are grouped by by catParent - tree-like or whatever ???

PS: I have been using Michael Dinowitz's recursion cfc which I can output like below and it makes a tree

<ul>
<cfoutput query="qRecurse">
      <li class="golinks"> #repeatString('     ',makeTreeSortLevel -1)#  <a href="#currentPage#?go=#pageName#"> #pageMenuText#</a></a></li>
</cfoutput>
</ul>

I can't remember now where I got the cfc (and can't find it on google at the moment) so here it is below

<!---  
This tag will take a query that is in a LEGAL parent/child relationship and sort it.
The entire query will be sorted and an additional field called "maketreesortlevel"
will be added to specify the level of a particular item.
Attributes:
     Query (Required, Query) - The query to be sorted
     qRecurse (required, string) - The variable name which will contain the sorted query
     Unique (optional, string, defaults to messagecounter) - The unique identifier of a
          query item. In older terms, the ItemID
     Parent (optional, string, defaults to ParentID) - The id of the parent for an item.
          MUST reference a legal parent. 0 is considered a root item. In older terms, the ParentItemID.

Author:     Michael Dinowitz
http://www.houseoffusion.com/
--->
<cfcomponent hint="This will recurse thru an adjacency list query and return a tree">
     <cffunction name="recurseAdjacencyList" access="public" output="false">
     <cfargument name="query" required="yes" hint="The query to be sorted">
     <cfargument name="unique" required="no" default="" hint="The unique identifier of the
          query item">
     <cfargument name="Parent" required="no" default="" hint="The id of the parent for an item">
     <cfset var qRecurse="">
          <cfscript>
          /*
          Convert Query to List. All work is done on this 'worklist'
          which is assigned to the request structure as well.
          */
          Items = evaluate('valueList(arguments.query.' & arguments.unique & ')');
          Parents = evaluate('valueList(arguments.query.' & arguments.parent & ')');
          // Set Local Query
          Local = queryNew(arguments.query.ColumnList & ',makeTreeSortLevel');

          Current = 0;
          Path = 0;
          // Loop over Query list and order in a tree
          for (index=1; index LTE arguments.query.recordCount; index=index+1){
               //Adds a new row
               queryAddRow(Local);
               //Sets the new row to the value for the list
               Position = listFind(Parents,Current);          
               //If the current ID has no children, drop down the path and try again
               while (NOT Position){
               Path = listRest(Path);
               Current = listFirst(Path);
               Position = listFind(Parents,Current);
               }
               for (i=1; i LTE listLen(arguments.query.columnList); i=i+1){
               Column = listGetAt(arguments.query.columnList,i);
               querySetCell(Local,Column,evaluate('arguments.query.' & Column & '[Position]'));
               }
               querySetCell(Local,'makeTreeSortLevel',listLen(path));
               Current = listGetAt(Items, Position);
               Parents = listSetAt(Parents, Position, '-');
               Path = listPrepend(Path, Current);
          };
          qRecurse = Local;
          </cfscript>
               <cfreturn qRecurse>
     </cffunction>
</cfcomponent>

thanks, Megan

We have art so that we shall not die of reality ~ Nietzsche

louissto56
10-24-2008 @ 12:54 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1149
Joined: Jan 2007

Demo here: http://www.mybizlist.com.au/recursive/

:)

-----------------------------
www.MyBizList.com.au - QLD Business Directory and rent-a-site

megan
10-24-2008 @ 12:38 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 2398
Joined: Jan 2003

thanks louis - so could you demo outputting this grouped by catParent ??? thanks, megan

We have art so that we shall not die of reality ~ Nietzsche

PAGE: 1 2

Website Designed and Developed by Pablo Varando.