Nested Loops

You can link 2 recorsets by a common field and display them in a nested format

For example, the roomservice table contains orders identified by the id column. The roomitem table contains the items that match each order and records the order in an orderid column. We can display orders and items in different formats and link items to the order the belong to.

Enter the number of orders you want to see (may be limited by the actual number in the database)

Query for orders stored in the roomservice table

We put the sql stmt in a CDATA section so we could use a <= in the stmt.

<dxp:DbAction value="query" dbcon="demo" store="orders">
 <dxp:SqlStmt name="stmt">
  <dxp:Cp name="sql"><![CDATA[select id, room, stamp from roomservice
   where id <= ?]]></dxp:Cp>
  <dxp:SqlParam value="${maxid}" type="NUMBER" require="true"/>
 </dxp:SqlStmt>
</dxp:DbAction>

Query for items stored in the roomitem table.

<dxp:DbAction value="query" dbcon="demo" store="items" colindex="0">
 <dxp:SqlStmt name="stmt"> 
  <dxp:Cp name="sql"><![CDATA[select orderid, name, cost from roomitem, roomservice
   where roomservice.id <= ? and roomitem.orderid=roomservice.id]]></dxp:Cp>
  <dxp:SqlParam value="${maxid}" type="NUMBER" require="true"/>
 </dxp:SqlStmt> 
</dxp:DbAction>

Create a column index on the orderid column of the items matrix. This will map items to orders.

<dxp:MatrixColumnIndex value="${items}" col="0" store="items.index"/> 

Print each order and use the items.index to find the appropriate items. Display items in a different format along with a subtotal.

<table border="0" cellspacing="2" bgcolor="#9999ff">
<dxp:LoopMatrix value="${orders}" cols="0,1,2">
 <tr bgcolor="#ffffff">
  <td>ID: ${0}</td>
  <td>Room: ${1}</td>
  <td colspan="2">${2}</td>
 </tr>
 <dxp:LoopRowset value="${items}" cols="0,1,2" prefix="i"
  map="${items.index}" key="${0}"> 
  <tr bgcolor="#eeeeff">
   <td colspan="2" align="center">orderid: ${i0}</td>
   <td>${i1}</td>
   <td align="right">${i2}</td>
  </tr> 
 </dxp:LoopRowset>
 <dxp:SumRowset value="${items}" cols="2" currency="true"
  map="${items.index}" key="${0}" store="subtotal"/>
  <tr bgcolor="#ffcccc">
   <td colspan="3" align="right">order total</td>
   <td align="right">${subtotal.2}</td>
  </tr>
</dxp:LoopMatrix>
 <tr bgcolor="#ffffff">
  <td align="center" colspan="4">${orders.count} Orders in query</td>
 </tr>
<dxp:SumMatrix value="${items}" cols="2" currency="true" store="total"/>
 <tr bgcolor="#ccffcc">
  <td colspan="3" align="right">Grand Total</td>
  <td align="right">${total.2}</td>
 </tr>
</table>