[Zope] - Re: Zope - YASQ (yet another stupid question)

[Amos Latteier]

At 04:17 PM 12/23/98 -0600, Chad Fowler wrote:
>Amos,
>
>Thanks for your encouraging response.  I think I should provide a little more
>detail.
>What I am specifically trying to do is look up a value from a DB table for
>every record
>returned from another DB table.
>For example, if I had a database of Overtime hours worked for company
>employees, for
>each record in the "OThours" table, I would look up "fullname" and
>"phonenumber" based
>on "employeenumber" out of the "Employees" table.

Sounds like what you really want is another SQL method that does a join on
the OThours and Employee tables.

>So, what I have done (and, I'm sure it's somehow flawed) is create a SQL
Method
>
>that returns multiple rows from the "OThours" table, and using the <!--#in
tab,
>looped
>through each one, displaying the data.  I'm trying to display the Employee
Name
>and Phonenumber
>from the "Employees" table instead of the employeenumber that is stored with
>each record in the
>"OThours" table.  The way I have approached this part is to create another
SQL
>Method called
>getName (for example) which takes an argument called "eid", and inside my
><!--#in loop, have something like the following:
>
><!--#in getAllOThours-->
>Hours work: <!--#var hoursworked-->
>Emp. Name: <!--#var expr="getName(eid=<!--#var employeenumber-->)"-->
><!-- yes, i realize the above definitely doesn't work, but you see what i'm
>getting at :) -->
>Mood: <!--#var mood-->
><!--#/in-->

OK, so if you really want to call another SQL Method for every row returned
by a given SQL Method, you're on the right track. I see two problems with
the above.

1 - The syntax is wrong as I mentioned in my last post. you should substitute:

    <!--#var expr="getName(eid=employeenumber)"-->
    for this:
    <!--#var expr="getName(eid=<!--#var employeenumber-->)"-->

2 - The getName SQL Method probably returns a collection of records, which
have attibutes. My guess is that you aren't looking to insert the whole
result set, but rather an attribute of the first returned record.

So my guess is that you want something like this:

<!--#in getOTHours-->
hours: <!--#var hours-->
<!--#in expr="getEmployee(eid=eid)"-->
fname: <!--#var fname-->
lname: <!--#var lname-->
<!--#/in-->
<!--#/in-->

Which assumes that getOTHours returns records with 'hours' and 'eid'
columns, and that getEmployee can be queried with 'eid' and returns records
with 'fname' and 'lname' columns.

Make sense?

-Amos