[Zope] Grouping ZSQL results

[Andy McKay]

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

What if you need the results over next 30 days or year?

If the query is returning the right thing, massaging it in a Python script
might be your easiest solution.
--
  Andy McKay
  Agmweb Consulting
  http://www.agmweb.ca

----- Original Message -----
From: "Meilicke, Scott" <scott.meilicke@intp.com>
To: "'Tim Wilson'" <wilson@visi.com>; "Zope list" <zope@zope.org>
Sent: Friday, September 20, 2002 3:20 PM
Subject: RE: [Zope] Grouping ZSQL results


> 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.
>
> _______________________________________________
> 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 )
>