On 05 Apr 2001 13:22:31 +0100, Tim Hicks wrote:

----- Original Message ----- From: "Gitte Wange" <gitte@mmmanager.org> To: <zope@zope.org> Sent: Thursday, April 05, 2001 1:08 PM Subject: [Zope] Putting a <dtml-in> tag into a <dtml-in> tag

...

Hmmm confusing subject :-)

What I need to do is this: Get some data out of my database (in a <dtml-in> tag) While iterating through this data, I need to get some more data out out my database (another <dtml-in> tag. I tried to call the second sql method with a <dtml-var> from the first sql method but I get an error.

So how do I collect some data from my database with a result from the first sql method ?

Wouldn't it be easier to put all the 'data collection' within one zsql method?

tim

Well I just thought of that :-) But actually I don't know how. Maybe someone here could help. I need to this this: First select all clips that belongs to a playlist from the playlist table (clips is identified by clipid's) Then select the specific clip-data from the clip table from the clipid Anyone ? -- ************************ Gitte Wange Jensen System Squid Developer MMManager Aps +45 29 72 79 72 gitte@mmmanager.org ************************

----- Original Message ----- From: "Gitte Wange" <gitte@mmmanager.org> To: "Tim Hicks" <tim@sitefusion.co.uk> Cc: <zope@zope.org> Sent: Thursday, April 05, 2001 1:32 PM Subject: Re: [Zope] Putting a <dtml-in> tag into a <dtml-in> tag

On 05 Apr 2001 13:22:31 +0100, Tim Hicks wrote:

...

----- Original Message ----- From: "Gitte Wange" <gitte@mmmanager.org> To: <zope@zope.org> Sent: Thursday, April 05, 2001 1:08 PM Subject: [Zope] Putting a <dtml-in> tag into a <dtml-in> tag

...

Hmmm confusing subject :-)

What I need to do is this: Get some data out of my database (in a <dtml-in> tag) While iterating through this data, I need to get some more data out

out

...

...

my database (another <dtml-in> tag. I tried to call the second sql method with a <dtml-var> from the first sql method but I get an error.

So how do I collect some data from my database with a result from the first sql method ?

Wouldn't it be easier to put all the 'data collection' within one zsql method?

tim

Well I just thought of that :-)

But actually I don't know how. Maybe someone here could help. I need to this this: First select all clips that belongs to a playlist from the playlist table (clips is identified by clipid's) Then select the specific clip-data from the clip table from the clipid

I'm not that great at sql, but I think you just want to use a 'join'. http://www.google.com/search?q=%2Bsql+%2Btutorial+%2Bjoin turns up fair number of tutorials that detail how to do this, but depending on which database you are using, I think the syntax may be slightly different. tim

On 05 Apr 2001 14:39:55 +0200, Paul Zwarts wrote:

Doesnt that depend on the tables? Zope doesnt support multiple selects in one method. Regarding the question, can you use a Zsql method through a VAR tag? It should look like this I think:

<dtml-in "sqlmethod1"> <dtml-in "sqlmethod2(a=varfromsqlmethod1)"> what do you mean here by a=varfromsqlmethod1? Should I putin here e.g. clipnum=clipid (clipid is the resulting variable from sqlmethod1) ? <dtml-var theResultfrom2> </dtml-in> </dtml-in>

I get an error if I try to <dtml-var> anything from the result of sql method 2. But if I delete sqlmethod2 and tries to print out clipid from sqlmethod1, I don't get the error. If I can print out clipid, then it must also be possible to call sqlmethod2 with this variable ? Regards, -- ************************ Gitte Wange Jensen System Squid Developer MMManager Aps +45 29 72 79 72 gitte@mmmanager.org ************************

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