[Zope] Grouping ZSQL results

Tim Wilson wilson@visi.com
Fri, 20 Sep 2002 15:54:17 -0500


Hi everyone,

I've built an event database using PostgreSQL and a number of ZSQL methods,
DTML methods, and Python Scripts. The following SQL returns a list of
all the events that will be happening in the next 7 days.

SELECT event.event_id, event_name, start_time, is_away, org_name,
activity_name
  FROM event_participant, event, participant, location
  WHERE
    <dtml-sqltest expr="ZopeTime().earliestTime()"
column="event.start_time" op="gt" type="string"> AND
    <dtml-sqltest expr="ZopeTime().latestTime() + 7"
column="event.start_time" op="lt" type="string"> AND
    <dtml-sqltest org_name column="participant.org_name" op="eq"
type="string"> AND
    event_participant.event_id = event.event_id AND
    event_participant.participant_id = participant.participant_id AND
    location.location_id = event.location_id
 ORDER BY start_time

I need to group the results by date so I can print out something like
the following:

Today
=====
Football vs. Lincoln High (7:00)
Tennis at Washington High (6:30)

Tomorrow
========
Volleyball tournament (8:00)

Monday, Sept. 23
================
PTA meeting (7:00)
Swimming vs. Jefferson High (7:00)

etc.

This will be displayed off to the side on my school's homepage.

I'm trying to figure out how I can group the events by day and use the
date as a heading. I don't think a single <dtml-in> will do the job, but
I can't decide on the best alternative strategy. Anyone have any ideas?

-Tim

-- 
Tim Wilson      |   Visit Sibley online:   | Check out:
Henry Sibley HS |  http://www.isd197.org   | http://www.zope.com
W. St. Paul, MN |                          | http://slashdot.org
wilson@visi.com |  <dtml-var pithy_quote>  | http://linux.com