couchdb views can only be sorted by their key.
On 11 March 2013 20:20, Traun Leyden <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 >>
