Mailing-List: contact user-help@couchdb.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@couchdb.apache.org
MIME-Version: 1.0
In-Reply-To: 
 <CACOpBnV=BtcJPY=RrQUjdM7ofTBB9d453_Y-+Fo0MmApAUNAbQ@mail.gmail.com>
References: 
 <CACOpBnU+uZmzwPmKWqDZAr_4YjXosWCvPx1w0Zjow=J1mF3WeA@mail.gmail.com>
	<CABvT1DFvMPTgrOOea9wUH1nDYRdG4F1LXgLsJ6rSkD6gnrBQkA@mail.gmail.com>
	<CACOpBnV=BtcJPY=RrQUjdM7ofTBB9d453_Y-+Fo0MmApAUNAbQ@mail.gmail.com>
Date: Mon, 11 Mar 2013 20:22:43 -0500
Message-ID: 
 <CABvT1DEKse1K8DaWeXCmradQwkTc7s-+4rGuSVTR8oPqu_xTbA@mail.gmail.com>
Subject: Re: Sort a query by a time-senstive criteria
From: Robert Newson <rnewson@apache.org>
To: "user@couchdb.apache.org" <user@couchdb.apache.org>
Content-Type: text/plain; charset=ISO-8859-1

couchdb views can only be sorted by their key.

On 11 March 2013 20:20, Traun Leyden <tleyden@signaturelabsinc.com> wrote:
> 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
>>