[Zope] Grouping ZSQL results

[Meilicke, Scott]

Would you consider running the query seven times, one for each day?

-----Original Message-----
From: Tim Wilson [mailto:wilson@visi.com]
Sent: Friday, September 20, 2002 1:54 PM
To: Zope list
Subject: [Zope] Grouping ZSQL results


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

_______________________________________________
Zope maillist  -  Zope@zope.org
http://lists.zope.org/mailman/listinfo/zope
**   No cross posts or HTML encoding!  **
(Related lists - 
 http://lists.zope.org/mailman/listinfo/zope-announce
 http://lists.zope.org/mailman/listinfo/zope-dev )
The information contained in this email message may be privileged and is
confidential information intended only for the use of the recipient or any
employee or agent responsible for delivering it to the intended recipient.
Any unauthorized use, distribution or copying of this information is
strictly prohibited and may be unlawful.  If you have received this
communication in error, please notify the sender immediately and destroy the
original message and all attachments from your electronic files.