incubator-couchdb-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Traun Leyden <tley...@signaturelabsinc.com>
Subject Re: Sort a query by a time-senstive criteria
Date Tue, 12 Mar 2013 01:20:36 GMT
Thanks!  I'll follow up w/ you off-list regarding the Cloudant-specific
approach.

I actually need to make this work on TouchDB, so still interested to hear
if anyone can recommend a good solution using pure a CouchDB approach.

On Mon, Mar 11, 2013 at 5:09 PM, Robert Newson <rnewson@apache.org> wrote:

> Since you're on Cloudant (probably shouldn't post to the couchdb user list
> tbh);
>
> index('purchase_date', purchase.time); // assuming purchase.time is an
> epoch integer.
>
> and use ?q=purchase_date:[earliest TO latest]&sort="purchase_date"
>
> where earliest and latest are replaced by the appropriate number of
> millis since epoch.
>
> B.
>
>
> On 11 March 2013 18:49, Traun Leyden <tleyden@signaturelabsinc.com> wrote:
> > I want to be able to sort a list of users by how much they've spent in
> the
> > last 6 months.
> >
> > The document has a structure of:
> >
> > {
> >   "type":"user",
> >   "purchases": [ {"type":"purchase","time":"2012-11-05
> > 17:37:52","spent":50}, ...]
> > }
> >
> > Here's how I'm planning on writing the index:
> >
> > var cutoffDate = new Date();
> > cutoffDate.setHours(0,0,0,0);
> > cutoffDate.setMonth(cutoffDate.getMonth() - 6);
> > var total6m = 0;
> > doc.purchases.forEach(function(purchase) {
> >   if((new Date(purchase.time)) > cutoffDate)) {
> >     spent6m += purchase.spent;
> >   }
> > }
> > index('spent6m', spent6m, {'store':'yes'});
> >
> > The disadvantage to this approach is that the indexed data quickly gets
> > stale, because "new Date()" is only evaluated at index time and not when
> > the query is run.   Essentially it breaks the CouchDB rule of not having
> > the view depend on external data.
> >
> > As a workaround, I'm thinking of adding a new field to the user object to
> > store the purchase amount for the last 6 months, as well as adding a
> > nightly process to update that field to keep the value from getting stale
> > as time progresses.
> >
> > Is this the recommended approach or is there a cleaner way?
> >
> > Thanks,
> > Traun
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message