[ZPT] Decomposing sorted SQL result into grouped HTML list or table?

[Troy Farrell]

Great question.  I would first consider where you are running this.  Is 
your zope server powerful enough, having enough RAM for holding the data 
set.  If you database server is stronger, I'd use the multiple querys. 
If they run on the same box, I'd do the Python Script method:

Disclaimer: Untested pseudocode, grossly inefficient because it 
duplicates what is potentially a large dataset

sortStats.py:

#assume possible statuses are known
stats = ('Backordered','Defective','Returned')

result = {}

for x in stats:
   result[x] = []

for x in sqlQuery:
   result[x.status].append(x.id)

return result

showStats.pt:

<dl tal:define="results here/sortStats;">
   <dt tal:repeat="status python:results.keys();"
       tal:content="status"/>
     <dd tal:define="list python:results[status];">
       <span tal:repeat="id list"
             tal:omit-tag="">
         <span tal:content="id" tal:omit-tag=""/>
         <span tal:condition="not:repeat/id/last"
               tal:omit-tag="">,</span>
       </span>
     </dd>
</dl>

That code may be braindead, and you might consider that I usually write 
it first and optimize second :(

Lastly, run some benchmarks and see which works better.

Troy

Jeff Kowalczyk wrote:
> I have a zSQLMethod that returns a list of IDs and status
> codes, intended as a summary and/or hyperlink index at
> the end of a Page Template displaying business activity.
> The items are the 'stragglers' that don't fit into the
> normal processing for the day.
> 
> ID      Status
> 12343   Backordered
> 12344   Backordered
> 12345   Backordered
> 12346   Backordered
> 12347   Defective
> 12348   Returned
> 12349   Returned
> 
> The sorted list will be heavily weighted to one status
> code, say "Backordered" in this example. Backordered
> would vary from 100+ to zero items depending on which
> day you rendered the page. Other status codes would only
> show a few items, and generally be intransient once set.
> 
> How can I use a sensible combination of TAL and python
> scripts and or ZTUtils to render either one of the two
> forms of summary list shown below:
> 
> (A - Label, <br> and concatenation of IDs in paragraphs,
> label hidden if no values)
> 
> Backordered:
> 12343, 12344, 12345, 12346
> 
> Defective:
> 12349
> 
> Returned:
> 12347, 12348
> 
> 
> (B - HTML Table, column displays wrapped text IFF
> values, column hidden otherwise to reclaim width)
> 
> +---------------+--------------+--------------+
> |Backordered    | Defective    | Returned     |
> +---------------+--------------+--------------+
> | 12343, 12344, | 12349        | 12347, 12348 |
> | 12345, 12346  |              |              |
> |               |              |              |
> +---------------+--------------+--------------+
> 
> 
> I'm open to suggestion of taking the results into
> python lists, using a sophisticated repeat TAL, etc.
> 
> Should I just bite the bullet and make multiple ZSQL
> results, one for each status code? I can certainly use
> them in other indicators throughout the app, but I'd like
> to strike a balance between hitting the database N times
> and passing large resultsets to several python scripts.
> 
> Anything specific you can relate about how I can do
> this without degenerating into ugly or inefficient
> code would be greatly appreciated. Thanks.