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

1: how to select distinct events from calendar

Posted by Tom Lazar on 06/01/03 01:34 AM

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?

thanks,

tom

[1] http://surprising-enterprises.de/

2: Re: how to select distinct events from calendar (response to 1)

Posted by Don Baccus on 06/01/03 02:09 AM

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

3: Re: how to select distinct events from calendar (response to 2)

Posted by Tom Lazar on 06/01/03 12:02 PM

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
....
</fullquery>

and then

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

4: Re: how to select distinct events from calendar (response to 1)

Posted by Don Baccus on 06/01/03 07:17 PM

You could just put the first query as a subselect in the from clause of the second, no?

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

5: Re: how to select distinct events from calendar (response to 1)

Posted by Jade Rubick on 06/02/03 06:40 PM

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.

6: Re: how to select distinct events from calendar (response to 1)

Posted by Don Baccus on 06/02/03 07:07 PM

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 06/05/03 09:05 AM

(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 06/05/03 10:22 AM

Dirk,

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