On Thu, 5 Apr 2001, Jens Vagelpohl wrote:
do yourself a big favor and use SQL to join the data... it will be cleaner and faster. joins are done like this (your syntax depends on your database):
select table1.column2, table1.column2, table2.column1 from table1, table2 where table1.my_key_field = table2.my_key_field
*Be careful* that you understand this: if you have a table of staff (Staff) and table of Goals those staff have (Goals), such that Staff.staffid = Goals.staffid, and you issue the query SELECT Staff.staffid, Staff.name, Goal.info FROM Staff, Goals WHERE Staff.staffid = Goal.staffid you're *ONLY* going to get those staff that *have* goals. A staff person without goals will not appear anywhere in the result set. Of course, sometimes this is what you'd expect and want, but often, people assume that they'll see everyone, even if they have no goals. To do that, you'll have to write what most databases call an 'outer join' query. The syntax can vary by database, but usually looks like: SELECT Staff.staffid, Staff.name, Goal.info FROM Staff LEFT OUTER JOIN Goals USING ( staffid ); -- More generally: You can use nested <dtml-in> tags, but, as your nesting grows, there can be serious performance overhead: <dtml-in Staff> <h1>StaffName</h1> <dtml-in "Goals({ 'staffid': staffid })"> <h2><dtml-var GoalName></h2> <dtml-in "GoalMetrics({ 'goalid': goalid })"> <dtml-var GoalMetricInfo> </dtml-in> </dtml-in> </dtml-in> (where Staff, Goals and GoalMetrics are ZSQL methods that pull records from staff, goal, and goal metric tables.) With 100 staff, 5 goals/staff, 5 metrics per goal, that's *2500* queries you're passing to your backend! Even with a fast database, like PostgreSQL or MySQL, that's can take a while! A better solution is to join in SQL, and use the first- variables so that this can be collapsed into a single list: <dtml-in Staff_Goals_and_Metrics> <dtml-if first-staffid> <h1><dtml-var StaffName></h1> </dtml-if> <dtml-if first-goalid> <h2><dtml-var GoalName></h2> </dtml-if> <dtml-var GoalMetricInfo> </dtml-in> (where Staff_Goals_and_Metrics is a ZSQL method that joins together the three tables into one flat result set.) This way, you're only looping over 2500 records of one query, which is much faster. If you're lucky enough to be using PostgreSQL, or another database that lets you define aggregate functions, you can move some of this to the database. I wrote a HOWTO last night that explains how to do this in PostgreSQL. It's at http://www.zope.org/Members/pupq HTH, -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington