You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@couchdb.apache.org by Traun Leyden <tl...@signaturelabsinc.com> on 2013/03/12 00:49:09 UTC
Sort a query by a time-senstive criteria
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
Re: Sort a query by a time-senstive criteria
Posted by Robert Newson <rn...@apache.org>.
couchdb views can only be sorted by their key.
On 11 March 2013 20:20, Traun Leyden <tl...@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 <rn...@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 <tl...@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
>>
Re: Sort a query by a time-senstive criteria
Posted by Traun Leyden <tl...@signaturelabsinc.com>.
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 <rn...@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 <tl...@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
>
Re: Sort a query by a time-senstive criteria
Posted by Jens Alfke <je...@couchbase.com>.
On Mar 11, 2013, at 5:09 PM, Robert Newson <rn...@apache.org> wrote:
> index('purchase_date', purchase.time); // assuming purchase.time is an
> epoch integer.
>
> and use ?q=purchase_date:[earliest TO latest]&sort="purchase_date"
I did not know about this. Found some docs: https://cloudant.com/for-developers/search/
—Jens
Re: Sort a query by a time-senstive criteria
Posted by Robert Newson <rn...@apache.org>.
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 <tl...@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