[Zope] Putting a <dtml-in> tag into a <dtml-in> tag

[Joel Burton]

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