You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@couchdb.apache.org by Nils Breunese <n....@vpro.nl> on 2009/07/03 13:44:16 UTC
Sorting view queries
Hello all,
We set out to use CouchDB as a piece of software that can easily be used
to create REST APIs. We publish documents to CouchDB, add some views and
show/list functions, put Apache httpd+mod_rewrite in front for the
pretty URLs and there is your super fast REST API!
Well, that was the idea, but we ran into an issue. We have a view that
returns events with start and end timestamps. It should be possible to
ask the API for a list of current events and events that are upcoming. I
thought I'd just emit the end timestamp as the key and query using
startkey=currenttime. This returns the correct events, but the list is
sorted by end timestamp.
The API would make a lot more sense if it would return a list sorted by
start timestamp, but I don't see a way to do this without adding some
server-side logic to sort the results from the view query. This would
mean adding another layer to the setup: more complexity and loss of
performance probably. Is it really not possible to accomplish this with
just CouchDB or do I miss some way of mapping and/or querying the data
which does yield the desired result?
Nils Breunese.
Re: Sorting view queries
Posted by Jason Davies <ja...@jasondavies.com>.
Nils,
On 8 Jul 2009, at 18:14, Jason Davies wrote:
> On 8 Jul 2009, at 15:11, Nils Breunese wrote:
>
>> Chris Anderson wrote:
>>
>>> The pattern used on http://jchrisa.net/cal/_design/cal/index.html is
>>> to emit once an hour for every hour the event is active, this way
>>> you
>>> can always query for current events (use 15 minutes or 1 minute
>>> granularity if that fits your model better)
>>
>> How do you 'emit once an hour'? Do you use a cronjob or something?
>
> I think he just means do something like:
>
> function (doc) {
> for (var d = doc.start_date; d < doc.end_date; d[3]++) {
> // do some stuff to push carries of hour up to day, month, year ...
> emit(d, ...);
> }
> }
>
> Here I'm assuming that doc.start_date and doc.end_date are in the
> form: [year, month, day, hour].
If you want to have a look at Chris's calendar app code, you should
install 'couchapp' and do:
couchapp clone http://jchrisa.net/cal/_design/cal
Oh the beauty of it! :-)
Cheers,
--
Jason Davies
www.jasondavies.com
Re: Sorting view queries
Posted by Jason Davies <ja...@jasondavies.com>.
Hi Nils,
On 8 Jul 2009, at 15:11, Nils Breunese wrote:
> Chris Anderson wrote:
>
>> The pattern used on http://jchrisa.net/cal/_design/cal/index.html is
>> to emit once an hour for every hour the event is active, this way you
>> can always query for current events (use 15 minutes or 1 minute
>> granularity if that fits your model better)
>
> How do you 'emit once an hour'? Do you use a cronjob or something?
I think he just means do something like:
function (doc) {
for (var d = doc.start_date; d < doc.end_date; d[3]++) {
// do some stuff to push carries of hour up to day, month, year ...
emit(d, ...);
}
}
Here I'm assuming that doc.start_date and doc.end_date are in the
form: [year, month, day, hour].
Hope that helps,
--
Jason Davies
www.jasondavies.com
Re: Sorting view queries
Posted by Nils Breunese <n....@vpro.nl>.
Chris Anderson wrote:
> The pattern used on http://jchrisa.net/cal/_design/cal/index.html is
> to emit once an hour for every hour the event is active, this way you
> can always query for current events (use 15 minutes or 1 minute
> granularity if that fits your model better)
How do you 'emit once an hour'? Do you use a cronjob or something?
Nils.
Re: Sorting view queries
Posted by Chris Anderson <jc...@apache.org>.
On Fri, Jul 3, 2009 at 9:37 AM, Geoff Buesing<ge...@geoffbuesing.com> wrote:
> On Fri, Jul 3, 2009 at 6:59 AM, Robert Newson <ro...@gmail.com>wrote:
>
>> wouldn't emitting the start timestamp as the key solve the problem?
>> (if you need both, have two views).
>
>
> That would just return upcoming events, it wouldn't return current events
> that have already started but have yet to end.
>
The pattern used on http://jchrisa.net/cal/_design/cal/index.html is
to emit once an hour for every hour the event is active, this way you
can always query for current events (use 15 minutes or 1 minute
granularity if that fits your model better)
> I think you could do this with couchdb-lucene, though, something like:
>
> q=starttime:[current_time TO far_future_time] OR endtime:[current_time TO
> far_future_time]
> &sort=starttime
>
--
Chris Anderson
http://jchrisa.net
http://couch.io
Re: Sorting view queries
Posted by Nils Breunese <n....@vpro.nl>.
Geoff Buesing wrote:
> I think you could do this with couchdb-lucene, though, something like:
>
> q=starttime:[current_time TO far_future_time] OR endtime:[current_time TO
> far_future_time]
> &sort=starttime
Thanks, guess I'll go and check out couchdb-lucene.
Nils.
Re: Sorting view queries
Posted by Geoff Buesing <ge...@geoffbuesing.com>.
On Fri, Jul 3, 2009 at 6:59 AM, Robert Newson <ro...@gmail.com>wrote:
> wouldn't emitting the start timestamp as the key solve the problem?
> (if you need both, have two views).
That would just return upcoming events, it wouldn't return current events
that have already started but have yet to end.
I think you could do this with couchdb-lucene, though, something like:
q=starttime:[current_time TO far_future_time] OR endtime:[current_time TO
far_future_time]
&sort=starttime
Re: Sorting view queries
Posted by Robert Newson <ro...@gmail.com>.
wouldn't emitting the start timestamp as the key solve the problem?
(if you need both, have two views).
On Fri, Jul 3, 2009 at 12:44 PM, Nils Breunese<n....@vpro.nl> wrote:
> Hello all,
>
> We set out to use CouchDB as a piece of software that can easily be used to
> create REST APIs. We publish documents to CouchDB, add some views and
> show/list functions, put Apache httpd+mod_rewrite in front for the pretty
> URLs and there is your super fast REST API!
>
> Well, that was the idea, but we ran into an issue. We have a view that
> returns events with start and end timestamps. It should be possible to ask
> the API for a list of current events and events that are upcoming. I thought
> I'd just emit the end timestamp as the key and query using
> startkey=currenttime. This returns the correct events, but the list is
> sorted by end timestamp.
>
> The API would make a lot more sense if it would return a list sorted by
> start timestamp, but I don't see a way to do this without adding some
> server-side logic to sort the results from the view query. This would mean
> adding another layer to the setup: more complexity and loss of performance
> probably. Is it really not possible to accomplish this with just CouchDB or
> do I miss some way of mapping and/or querying the data which does yield the
> desired result?
>
> Nils Breunese.
>