CODEWITHSUNDEEP
coldfusion
user160820
user160820

Reputation: 15210

Coldfusion Looping over query

I have a query result something like

ID IDParent Name    Title
--------------------------------------
1  -1   Test1       Test1_Title 
2  -1       Test2       Test2_Title
3  -1       Test3       Test3_Title
4   2   SubTest2    SubTest2_Title
5   2   SubTest3    SubTest3_Title
6   2   SubTest4    SubTest4_Title
7   3   SubTest6    SubTest8_Title
8   3   SubTest8    SubTest10_Title

with menu and submenu options.I want to loop over the menu option where IDParent is -1 and after each menu Item where IDParent -1 I would like to loop its child items.

Does coldfusion provides such grouping when looping over queries?

Thanks

Upvotes: 0

Views: 1109

Answers (4)

Sujin S
Sujin S

Reputation: 21

consider qTestQuery contains the values

<cfset qTestQuery1 = qTestQuery>
<cfloop query="qTestQuery">
    <cfif qTestQuery.IDParent eq -1>
        <span class="main-menu">#qTestQuery.name#</span>
    </cfif>
    <cfset local.parentId = qTestQuery.IDParent>
    <cfloop query="qTestQuery1">
         <cfif qTestQuery1.IDParent eq local.parentId>
              <span class="sub-menu">#qTestQuery1.name#</span>
         </cfif>
    </cfloop>
</cfloop>

Upvotes: 0

barnyr
barnyr

Reputation: 5678

If you have any control of the SQL generating that query result, you could consider getting the DB to get you the data back in the right format in the first place. Approaches for Oracle and SQL server are covered here and there's some options for mySQL here

If your menu data is always going to be small, then there'll be no problem with Tomalak's solution, but if you're ever going to have large numbers of menu items then I'd test that it still performs ok.

Upvotes: 1

Sharondio
Sharondio

Reputation: 2614

CFOUTPUT supports query groupings as well.

<cfquery name="qGetTests" datasource="#DSN#">
  SELECT   ID, IDParent, Name, Title
  FROM     Menu
  ORDER BY IDParent, Name
</cfquery>

<cfoutput query="qGetTests" group="IDParent">
    #IDParent#<br />
    <cfoutput>
       #ID# #Name# #Title#<br />
    </cfoutput>
</cfoutput>

Upvotes: 5

Tomalak
Tomalak

Reputation: 338208

That's pretty easy with Query of Queries (QoQ) and a little recursion:

<!-- database query, sorted in the way you want to display the results -->
<cfquery name="Menu" datasource="#YourDSN#">
  SELECT   ID, IDParent, Name, Title
  FROM     Menu
  ORDER BY Name
</cfquery>

<!-- output menu -->
<cfset OutputQueryRecursive(Menu, -1)>

<!-- support function -->
<cffunction name="OutputQueryRecursive">
  <cfargument name="BaseQuery" type="query"   required="yes">
  <cfargument name="ParentId"  type="numeric" required="yes">

  <cfquery name="CurrLevel" dbtype="query">
    SELECT * FROM BaseQuery WHERE IDParent = #ParentId#
  </cfquery>

  <cfif CurrLevel.RecordCount gt 0>
    <ul>
      <cfoutput query="CurrLevel">
        <li id="menu_#ID#">
          <span title="#HTMLEditFormat(Title)#">#HTMLEditFormat(Name)#</span>
          <cfset OutputQueryRecursive(BaseQuery, ID)>
        </li>
      </cfouptut>
    </ul>
  </cfif>
</cffunction>

Upvotes: 4

Related Questions