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 last

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

Hello All,

Tonight I decided to create a CFC to build a mysql
query for recursive tables such as categories. Normally
people would have endless queries of queries or some
other performance intensive code, where as this puts
together the appropriate amount of JOINS in one query.
Quick and easy Smile  If you want to have a look at it,
download this file: www.mybizlist.com.au/recursiveSQL.zip and run the dump
file in one of your databases. Then just put in your
datasource name in index.cfm (you'll see what I mean
when you open it). Then run it!

Please crit it.
Thanks,
Louis

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

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

PAGE: 1 2

Website Designed and Developed by Pablo Varando.