[Zope] How do I create a cursor on a query in Zope

[Evan Simpson]

Ah! Ok, the first approach which pops to mind is this:

Suppose SQL_get_names is the first query, and the others are SQL_get_count1,
..., SQL_get_countn.  You could then write:

<dtml-in SQL_get_names>
  <dtml-var NAME> <dtml-in SQL_get_count1><dtml-var count></dtml-in><dtml-in
SQL_get_count2>...</dtml-in>
</dtml-in>

Next approach; Suppose queries 1..n differ only in the table name and group
field.  Define SQL_get_count(table, gfield, NAME) as:

select <dtml-var gfield>, count(*) as rcount from <dtml-var table>
where <!--#sqltest name=NAME col=name type=string>
group by <dtml-var gfield>

and write:

<dtml-in SQL_get_names>
  <dtml-var NAME> <dtml-in "('table1', 'field1'), ('table2', 'field2'),...">
    <dtml-in "SQL_get_count(table=_['sequence-key'], gfield=_['sequence-item'],
NAME=NAME)"><dtml-var rcount></dtml-in>
  </dtml-in>
</dtml-in>

Finally, if you want to minimize the number of queries, you could try defining
SQL_get_count(table, gfield) as a join query, and use a dictionary and pair of
lists to store results.  That gets pretty hairy to write in DTML, though.

Marten Quadland wrote:

> Sorry for the confusion,
>
> I'm trying to open a cursor on a query say:
>
> Open a cursor:
> select NAME from table
>
> Where I can read one name at a time and perform an action on that name.  For
> example:
>
> select blah, count(*) as table2Count from table2
> where table2.name = NAME
> group by blah
>
> select blah2, count(*) as table3Count from table3
> where table3.name = NAME
> group by blah2
>
> select blah3, count(*) as table4Count from table3
> where table3.name = NAME
> group by blah3
>
> Write a row(NAME, table2count, table3count, table4count)
>
> CLOSE THE CURSOR
>
> The end result would be something like:
>
> NAME            Count of Visits Count of Purchases      Count of Inquiries
> Joe                     1234                    4331
> 5965
> Jane                    3766                    3534
> 35
> Jeff                    3457                    6433
> 3653
> Harry                   5433                      88
> 2854
> Tim                       56                    2434
> 9865
> .
> .
> .
>
> The capitalized NAME is equal to the value of the first response from
> Query1.