Forum OpenACS Q&A: how to select distinct events from calendar

hi there,

i'm displaying a list of current events of our community-site [1] on the front page as an overview.

it works great except for events that run longer than one day.

the only way (i know of) to enter multiple-day events is to create a repeating (daily) event for the number of days (which is fine by me...)

however, this means, that these events appear n-times in the summary which is not nice at all (consider a month-long festival!!)

now, using the DISTINCT selector in SQL doesn't fix anything, since the multiple entries of the (same) event have unique dates (obviously).

has anyone got a suggestion?




Posted by Don Baccus on
The recurrance_id in acs_events is shared for all the entries, so you could group on that and pick the minimum calendar_id to get the first entry for the recurring event.

Something like that, anyway, should give you a starting place.

You've hit one of the shortcomings of the calendar datamodel ... there should be one acs_event entry only and calendar item should be a many-to-one map of time descriptions to that single acs_event.

Someday we're going to rewrite this package ...

Posted by Tom Lazar on
thanks again don,

one question, though: would it make sense (or is it even common practice?) to do the grouping in the .xql statement and THEN (re)-select from that in the .tcl file?

or can i have "nested" queries in the .xql file ala

  <fullquery name="grouped_events">
... select distinct recurrance_id, calendar_id from acs_events

and then

  <fullquery name="single_events">
... select from grouped_events, acs_events

Posted by Don Baccus on
You could just put the first query as a subselect in the from clause of the second, no?

select ... from (select distinct recurrance_id ..), ...

Posted by Jade Rubick on
I believe Dirk is talking about rewriting the calendar package. Not sure if he's committed to it yet.

We should bear this in mind for the next calendar rewrite.

Posted by Don Baccus on
Well, someone's going to have to rewrite it, there's no doubt about that.
7: Re: calendar rewrite (response to 1)
Posted by Dirk Gomez on
(I'm behind a slow dial-up connection at the moment and I just made my
winmodem work under Linux, hence the late reply)

Yeah, I grabbed ownership for the calendar package.

Here's a quick summary of how I want to work:

* Firstly migrate html generation from tcl procs to adp pages (make it comply
  with the templating system)

* Then hand off the presentation code to someone else better acquainted with
  html :)

* Look at the data model and redo it

* Supply customizable upgrade scripts

Currently calendar is *very* broken and definitely not ready for production
use. The postgres version for example chokes on a few pages, the data model
looks very inefficient, the code is really convoluted etc.

I'm also taking suggestions, Jade already mailed some. I'll summarize and
order them somehow. I've also drafted some things of my own and I will put
them up for discussion in a few days.

8: Re: calendar rewrite (response to 7)
Posted by Jarkko Laine on

One thing would probably be changing the calendar to use ad_form.