incubator-couchdb-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Robert Newson <rnew...@apache.org>
Subject Re: Sort a query by a time-senstive criteria
Date Tue, 12 Mar 2013 00:09:24 GMT
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
View raw message