 |
||
FORMATTING NESTED DATA IN TEMPLATES
Examining The Retrieved Data
If you are familiar with Perl, and you can interpret data structures composed of arrays and hashes, you may examine the structure of the retrieved data by asking Template Toolkit to write the data structure to your browser. Simply insert the following code into any template that you expect has access to the nested table information (such as supplier_data.ttml or supplier_page_top.ttml if you are dealing with Suppliers):
[% USE Dumper %] <pre>[% Dumper.dump_html(alias.nested_info) %]</pre>
The data is passed to the templates as the hash named alias.nested_info
The keys of this hash are the foreign key columns that have nested tables associated with them (even if the nesting depth is just 1). The values of the hashes are again hashes with one key/value pair: the name of the table associated with the foreign key, and the hash of structured data.
As an example, the output of the Dumper command shown above might look like the following, with each table hash containing 2 elements: an info hash with the values of the record, and another hash named for the table of subrecords, which contains the records at the next level.
$VAR1 = {
'menu_id' => {
'Menu' => [
{
'info' => {
'name' => 'Tony\'s Town Square Menu',
'id' => '1'
},
'MenuMeal' => [
{
'info' => {
'end_time' => '2.00 PM',
'menu_id' => '1',
'start_time' => '12.00 PM',
'name' => 'Lunch Menu',
'id' => '1'
},
'MenuSection' => [
{
'info' => {
'meal_id' => '1',
'name' => 'Appetizers',
'id' => '1',
'description' => undef,
'section_image' => '28'
},
'image_data' => {
'section_image' => {
'image' => {
'alt' => '',
'height' => '111',
'title' => '',
'url' => 'http://some/path/to/MenuSection/section_image/c/4/c/appetizer.jpg',
'width' => '111'
},
'pos' => '5',
'thumbnail' => {
'alt' => '',
'height' => '111',
'title' => '',
'url' => 'http://some/path/to/MenuSection/section_image/c/4/c/appetizer.jpg',
'width' => '111'
}
}
},
'MenuSelection' => [
{
'info' => {
'section_id' => '1',
'name' => 'Calamari',
'id' => '1',
'price' => '5.99',
'description' => 'Tossed with olive relish, and served with...'
}
},
{
'info' => {
'section_id' => '1',
'name' => 'Garlic Breadsticks',
'id' => '2',
'price' => '2.49',
'description' => 'Served with tomato sauce.'
}
}
]
}
]
}
]
}
]
}
};
This is a truncated data structure, just long enough for you to see the basic structure of the records. In fact, we see in the structure above an 'Appetizer' section, containing just 2 menu selections, for Calamri and Garlic Breadsticks. The full data structure might contain a hundred or more table records, from the various tables involved.
Notice the lettercase of the table names, and the columns too (in fact). This reflects the lettercase in the table definition files. Don't forget that. Get the letter case wrong when you are extracting data and you will not see any data appear in your templates.
Notice also that the only image column in the extracted data shown above is MenuSection.section_image and the data relating to that uploaded image is available in the image_data part of the record. This means that the image URL is avalable to us to construct an image tag. Both the original image and a thumbnail version of it are generally available. In this instance the thumbnail refers to the original image as it was too small to require thumbnailing. The value for the MenuSection.section_image column seen above, which is 28, is actually the PRIMARY KEY value for the uploaded image file found in the Upload table. But the only data you will care about is to be found in the image_data hash: image URLs and physical dimensions, should you need to place the image on the page.
Looping Through The Data With Template Toolkit
In practice we are not interested in dumping the data structure. We want to format the data nicely so that it is easy to interpret. We can use the power of Template Toolkit to do that for us in the templates. Below is sample code for the extraction of the data we have been using in this tutorial, where our menu data is captured in 4 different nested tables. The name of the template in this case would be supplier_nested.ttml (it is up to you to create that template with the relevant extraction code).
You will notice that the code for pulling the records requires that we know the names of the tables involved. This is generally not a problem as you will need more that passing familiarity with the data to do anything useful with it.
<!-- begin: supplier_nested.ttml -->
<!-- ADD SOME CSS STYLING FOR THE DIFFERENT MENU COMPONENTS -->
<style type="text/css">
.meal {
background: #3a515f;
color: #ffffff;
font-size: 16pt;
font-weight: bold;
padding: 4px;
}
.mealinfo { font-size: 10pt; }
.section {
background: #7897ab;
color: #ffffff;
font-size: 14pt;
font-weight: normal;
padding: 4px;
}
.sectioninfo { font-size: 10pt; }
.selection { font-size: 10pt; padding: 2px; }
</style>
<!-- LOOP OVER THE NESTED DATA ARRAYS -->
[% FOREACH menu = alias.nested_info.menu_id.Menu %]
<br/><br/>
<table align="center" width="90%" cellpadding="0" cellspacing="0" border="0"
style="font-family: Verdana, Tahoma, sans-serif; font-size: 10px;">
<tr> <td colspan="2" style="font-size: 18pt;"><b>[% menu.info.name %]</b>
</td></tr>
[% FOREACH meal = menu.MenuMeal %]
<tr> <td colspan="2"> </td></tr>
<tr> <td colspan="2" align="center" class="meal">[% meal.info.name %]
<br/><span class="mealinfo">
Served: [% meal.info.start_time %] - [% meal.info.end_time %]</span>
</td></tr>
[% FOREACH section = meal.MenuSection %]
<tr> <td colspan="2" align="center" class="section">[% section.info.name %]
[%- IF section.info.description %]
<br/><i class="sectioninfo">[% section.info.description %]</i>
[%- END %]
</td></tr>
[% FOREACH selection = section.MenuSelection %]
<tr> <td colspan="2"></td>
<tr> <td class="selection"><b>[% selection.info.name %]</b>
[%- IF selection.info.description %]
<i>[% selection.info.description %]</i>
[%- END %]
</td>
<td class="selection" align="right" valign="top">
[%- IF selection.info.price %]$[% selection.info.price %][% END %]
</td></tr>
[%- END -%]
[%- END -%]
[%- END %]
</table>
[% END %]
By the way, if you are wondering why some of the tags have a dash at the beginning or end of them, like this: [%- END %] it is because Template Toolkit will remove whitespace before or after the tag depending on whether it finds a dash at the beginning or end of the tag (templates can generate lots of whitespace, so using these dashes can be useful).
The End Result Of Formatting: A Restaurant Menu
Finally, here is the result of our parsed template for a non-trivial menu, using the Template Toolkit code shown above:
| Tony's Town Square Menu | |
| Lunch Menu
Served: 12.00 PM - 2.00 PM |
|
| Appetizers | |
| Calamari Tossed with olive relish, and served with side of tomato sauce. | $5.99 |
| Garlic Breadsticks Served with tomato sauce. | $2.49 |
| Mixed Greens Mixed Greens tossed in our House dressing topped with Feta cheese and croutons. | $4.19 |
| Soup of the Day Tony's Creation: soup of the day. | $4.19 |
| Sandwiches, Pizza and Salad
All sandwiches accompanied with fresh mixed greens. | |
| Meatball Submarine Meatballs, caramelized onions, and tomato sauce topped with melted Provolone cheese, and mozzarella cheeses. Accompanied by fresh mixed greens. | $10.89 |
| Grilled Chicken Panini Topped with bacon, Asiago cheese, and caramelized onions on Multigrain bread. Accompanied by fresh mixed greens. | $10.89 |
| Vegetable Calzone Filled with seasonal vegetables and fresh mozzarella. | $10.19 |
| Tony's Pizza Smoked chicken, caramelized onions, fontina, spinach and red pepper sauce. | $11.99 |
| Caesar Salad with Chicken Romaine lettuce and grilled chicken tossed in Caesar dressing. | $11.19 |
| Grilled Italian Panini Capicola, sopressata, prosciutto and provolone cheese on multigrain bread. | $10.89 |
| Lady and the Tramp's Combination Half panini with your choice of Mixed Greens, or Soup of the day. | $8.19 |
| Pizza Bianca Blend of six cheeses, garlic, basil, oregano. | $11.99 |
| Chicken Parmigiana Hero Lightly breaded chicken breast, tomato sauce topped with melted provolone and mozzarella cheeses. | $10.89 |
| Grilled Salmon Over mixed greens with beets, feta, and candied walnuts, tossed in an orange-shallot vinaigrette. | $14.19 |
| Pasta | |
| Pasta Primavera Grilled seasonal vegetables in garlic and olive oil topped with balsamic glaze and feta cheese. | $13.69 |
| Spaghetti With choice of meatballs or sweet Italian sausage in tomato sauce. | $12.19 |
| Shrimp Pasta Orecchiette pasta, roasted red peppers, and spinach tossed in a pesto-cream sauce. | $15.19 |
| Baked Ziti With meat sauce and ricotta topped with melted mozzarella. | $12.69 |
| Beverages | |
| Sodas, Milk, Nestea Iced Tea | $2.19 |
| Evian 1 Liter or Perrier 750 ML | $6.50 |
| Hot Tea | $2.19 |
| Coffee | $2.19 |
| Espresso | $3.19 |
| Cappuccino | $3.69 |
| That's Amore Cooler Fall in love with our refreshing smoothie of the day served in a Lady and the Tramp Collector's mug. | $7.99 |
| Dinner Menu
Served: 6.00 PM - 9.00 PM |
|
| Appetizers | |
| Tomato and Mozzarella Salad Tomatoes, fresh Mozzarella and Red Onions tossed in House Dressing. | $8.69 |
| Calamari Tossed with Olive Relish and drizzled with Caper Aioli. | $7.99 |
| Spinach and Artichoke Dip With Three-Cheese Blend and Tomatoes served with Grilled Bread. | $7.69 |
| Mixed Greens Tossed with our House Dressing topped with Feta and Croutons. | $4.69 |
| Soup of the Day | $4.69 |
| Entrées | |
| Seafood Diavolo Clams, Mussels, Calamari, Salmon, and shrimp in a Spicy Tomato Sauce. | $22.99 |
| Grilled Salmon With Asparagus, Black and Green Olives, Grape Tomatoes, Spinach, Pecans, Bacon, and Basil Pesto. | $23.49 |
| Beef & Spinach Cannelloni Served with Basil-Cream Sauce. | $19.49 |
| Spaghetti With choice of Meatballs or Sweet Italian Sausage in Tony's own Tomato Sauce. | $18.99 |
| Chicken Fiorentina Topped with Prosciutto, Spinach, and Melted Cheese, served with Roasted Potatoes and Marsala Mushroom Sauce. | $22.49 |
| Chicken Pasta Orecchiette pasta with roasted peppers and spinach tossed in a pesto-cream sauce. | $19.99 |
| New York Strip Steak Served with Roasted Potatoes, Mushrooms, Caramelized Onions, and Spinach Tossed in a warm Pancetta Vinaigrette, topped with Red Wine Gorgonzola Butter. | $25.49 |
| Eggplant Rotini Roulade of Eggplant, Ricotta,Tomato Sauce, and Mozzarella. | $19.49 |
| Dolce - Dessert? | |
| Pistachio Creme Brûlée | $5.69 |
| Lemon Walnut Layer Cake Layered vanilla cake filled with lemon curd. | $5.69 |
| Ice Cream Bomb Chocolate and vanilla ice cream covered in rich chocolate sauce. | $5.69 |
| Tiramisu Espresso soaked lady fingers, whipped mascarpone and dark chocolate shavings. | $5.69 |
| Amaretto Cheesecake With whipped cream. | $5.69 |
| Assorted Gelato Seasonal flavors of Italian style ice cream. | $5.69 |
| Beverages | |
| Sodas, Milk, Nestea Iced Tea | $2.19 |
| Evian 1 Liter or Perrier 750 ML | $6.50 |
| Hot Tea | $2.19 |
| Coffee | $2.19 |
| Espresso | $3.19 |
| Cappuccino | $3.69 |
| That's Amore Cooler Fall in love with our refreshing smoothie of the day served in a Lady and the Tramp Collector's mug. | $7.99 |
| Children's Menu / Lunch & Dinner
Served: 12.00 PM - 6.00 PM |
|
| Entrées | |
| Chicken Strips Served with BBQ sauce, sweet potato sticks, green beans and carrots, and yogurt parfait. Includes choice of Kids' juice, 1% milk, or soft drink. | $7.49 |
| Just Dip It Assorted fruits, vegetables, chicken salad, and yogurt. Includes choice of Kids' juice, 1% milk, or soft drink. | $7.49 |
| Pita Pizza Made with diced chicken and mozzarella cheese served with shredded vegetable salad and brown-sugar apples. Includes choice of Kids' juice, 1% milk, or soft drink. | $7.49 |
Adding The Data To A Profile Page
Given that you have created a template to unravel your nested table data and format it nicely, in, say, the supplier_nested.ttml template, how do you integrate that template into, say, the Supplier Profile page? Well, here's Template Toolkit code to do it for you (simply add it to the general location of the other table rows present in the supplier_data.ttml template):
[% IF alias.nested_info.keys.size > 0 %] <tr><td colspan="3" align="right" style="background: [% cell_dark %]; border-top: 1px solid [% border_color %]; border-bottom: 1px solid [% border_color %];"> <b><!-- setfont_base(+0) --> [% lang.supplier_profile.nested_header || 'MENU' %] <!-- setfont_end --></b> </td></tr> <tr><td colspan="3" style="background: [% cell_pale %]"> [% INCLUDE supplier_nested.ttml %] </td></tr> [% END %]
Sorting The Data
By default, the sorting of records is performed on the value of the PRIMARY KEY. If you are using an AUTO_INCREMENT column to generate the PRIMARY KEY value, this means that the last record added is the last one to appear in a list of extracted records. Generally this is exactly what you want. However, if you later decide to add a new record, and you want it to appear in a position other than at the end of the list, you have a problem.
To get around this issue, you can add a sort_order column to your nested table. When you do this, records will first be sorted on the increasing value of the sort_order column, and then on the increasing value of the PRIMARY KEY (which is the default sort order). It is recommended that you make the sort_order column an INTEGER column type.
Do not bother to add a sort_order column to your nested table UNLESS you absolutely need it, because it will then require you to add a suitable value for every record with the same foreign key value (since records are grouped by the foreign key column). But this is not really a big problem, since in general you might only have a half dozen records that are grouped together and require explicit sort_order values. Every other sort_order value in the table can be left as NULL or 0 (zero).
Pulling The Data For Pop Up Windows
One thing you may wish to do, instead of simply dumping your nested data construct into the Profile or Detail page (because, let's face it, not everyone needs to see the information) is construct a popup window to present your nested table structure (e.g. a restaurant menu), but only when a user clicks on a link requesting the information. In this case you can add javascript to the templates and create popup windows with destination URLs like the following:
redqueen.cgi?module=nested&item_id=$item_id&nested_col=$nested_col
redqueen.cgi?module=nested&member_id=$member_id&nested_col=$nested_col
redqueen.cgi?module=nested&supplier_id=$supplier_id&nested_col=$nested_col
where
(a) $item_id, $member_id, and $supplier_id are the PRIMARY KEY values
for an Item, Member, or Supplier record.
(b) $nested_col is the name of the foreign key column in the same table
As an example, to retrieve the restaurant menu for the Supplier record with id = 1
redqueen.cgi?module=nested&supplier_id=1&nested_col=menu_id
When this URL is accessed the restaurant menu is presesnted without any of the navigation information that accompanies other pages. In this respect the URL behavior is similar to a Red Queen SSI call. To give an explicit pop up window implementation, here is the javascript for a pop up window which is activated by clicking on a link.
Note: This code might be placed into supplier_page_top.ttml or supplier_data.ttml (which is incorporated into the Supplier Profile page):
<SCRIPT language="javascript">
<!--
function open_restaurant_menu() {
var width = 700;
var height = 500;
var window_features
= 'height='+height+',width='+width
+ ',scrollbars=1,resizable=auto,toolbar=0';
var target_url
= "redqueen.cgi?module=nested"
+ "&supplier_id=[% supplier.id %]"
+ "&nested_col=menu_id";
var menu_window
= window.open(target_url,'resturant_menu',window_features);
if (menu_window == null) { window.close() }
menu_window.focus();
}
// -->
</SCRIPT>
<a href="javascript: open_restaurant_menu();">Click To See Our Menu</a>
Note: The page title in this case will be taken from the 'name' column for the upper table associated with the nested data. So in our case, the page title is taken from the Menu.name column, if it exists (and would be Tony's Town Square Menu for the example shown above).
This wraps up our discussion of nested tables and the restaurant menu problem. You should now be able to use the techniques discussed in this tutorial to capture, extract, and format your own complex data structures.
Copyright © 2004 Random Mouse Software. All Rights Reserved.