You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@couchdb.apache.org by Fabio Forno <fa...@gmail.com> on 2009/07/30 02:38:15 UTC
counting rows in range queries
Hi,
I've read many threads explaining how to count rows in view results,
however I can't find any working solution for this particular case.
I've a set of items divided in buckets, and each item has a posting
date. I want to divide the items per bucket and sort them by their
date, so I make a view with this key:
[item.bucket, item.date]
Now the difficult part. There are many consumers who retrieve the
items from the buckets and each consumers knows the date of the last
item it has retrieved, so I do a range query on the view with
startkey=[item.bucket, last_date], endkey=[item.bucket, stopper]. This
is fine, but if I want to tell the user how many items it has to
retrieve before actually downloading them I can't do nothing but
retrieving all the rows and start counting. Any idea for solving this
with a proper map reduce, or some clever doc structure?
The only possible solution I've found so far is storing marker
documents for each possible consumer (containing just the item id) any
time I post a new item in a bucket. These can be counted with a
map/reduce and deleted when the items are retrieved, but I really
don't like the solution since it requires too many writes.
bye!
--
Fabio Forno, Ph.D.
Bluendo srl http://www.bluendo.com
jabber id: ff@jabber.bluendo.com
Re: counting rows in range queries
Posted by Patrick Aljord <pa...@gmail.com>.
// Number of rows
http://127.0.0.1:5984/db_name/_design/foo/_view/bar?startkey=[bucket,last_date_seen]&endkey=[bucket,blocker]
Maybe he needs to add a group=true if he's using an old version of couchdb.
Re: counting rows in range queries
Posted by Fabio Forno <fa...@gmail.com>.
On Thu, Jul 30, 2009 at 9:15 AM, Paul Davis<pa...@gmail.com> wrote:
> On Thu, Jul 30, 2009 at 2:53 AM, Fabio Forno<fa...@gmail.com> wrote:
>> On Thu, Jul 30, 2009 at 3:22 AM, Paul Davis<pa...@gmail.com> wrote:
>>>>
>>>
>>> My bad, that should read 'emit([doc.bucket, doc.date], value)'
>>>
>>
>> Well but it doesn't work either, since when I retrieve the view I get
>> as many rows as the selected docs, with count 1. Perhaps my
>> description was confusing, since date is not something like YYMMDD,
>> but YYMMDDhhmmss. Therefore all the emitted keys are different and the
>> reduce function can't do any aggregation.
>>
>
> This doesn't matter at all.
>
Why? I've got these functions, which are pretty similar to your ones:
map:
function(doc) {
if(doc.type == "item") {
emit([doc.node, doc.data.updated], null);
}
}
reduce:
function(key, values, rereduce) {
if(rereduce) {
return sum(values);
} else {
return values.length;
}
The node is the bucket name, updated is when the item has been
inserted (converted to seconds from the epoc). Then I retrieve this
view (cut & paste from the browser), in which I want all the docs
newer then 1248650362
http://zafferano.ooros.priv:5984/test-rss/_design/items/_view/test_count?group=true&startkey=[%22https://identi.ca/api/statuses/public_timeline.rss%22,%201248670629]&endkey=[%22https://identi.ca/api/statuses/public_timeline.rss%22,%20%22%22]
What I get is:
{"rows":[
{"key":["https://identi.ca/api/statuses/public_timeline.rss",1248670629],"value":1},
{"key":["https://identi.ca/api/statuses/public_timeline.rss",1248670630],"value":1},
{"key":["https://identi.ca/api/statuses/public_timeline.rss",1248670634],"value":1},
{"key":["https://identi.ca/api/statuses/public_timeline.rss",1248670682],"value":1},
{"key":["https://identi.ca/api/statuses/public_timeline.rss",1248670704],"value":1},
{"key":["https://identi.ca/api/statuses/public_timeline.rss",1248670718],"value":1},
{"key":["https://identi.ca/api/statuses/public_timeline.rss",1248670729],"value":1},
{"key":["https://identi.ca/api/statuses/public_timeline.rss",1248670747],"value":1},
{"key":["https://identi.ca/api/statuses/public_timeline.rss",1248670773],"value":1},
{"key":["https://identi.ca/api/statuses/public_timeline.rss",1248670781],"value":1},
{"key":["https://identi.ca/api/statuses/public_timeline.rss",1248670789],"value":1},
{"key":["https://identi.ca/api/statuses/public_timeline.rss",1248670801],"value":1},
{"key":["https://identi.ca/api/statuses/public_timeline.rss",1248670803],"value":2},
{"key":["https://identi.ca/api/statuses/public_timeline.rss",1248670805],"value":1},
{"key":["https://identi.ca/api/statuses/public_timeline.rss",1248670806],"value":1},
{"key":["https://identi.ca/api/statuses/public_timeline.rss",1248670813],"value":1},
{"key":["https://identi.ca/api/statuses/public_timeline.rss",1248670835],"value":1},
{"key":["https://identi.ca/api/statuses/public_timeline.rss",1248670839],"value":1},
{"key":["https://identi.ca/api/statuses/public_timeline.rss",1248670842],"value":1}
]}
I really can't understand what I'm doing wrong...
>
> Total rows always reflects the number of rows in the view. It can
> cause some confusion, but the other way around I don't know that it'd
> cause any less.
>
> Also, are you checking the value of the assumed single return row? If
> you're not getting a single returned row then something is wrong. If
> you are and it's wrong, then its wrong or I'm wrong. Either way, I'm
> confused...
>
Indeed... I'm not getting a single returned row and I can't figure out
how it can happen since date changes for each row
(It is clear to me how count all the items in a bucket, but not how to
do it in a range of dates specified in the query paramentes...)
bye
--
Fabio Forno, Ph.D.
Bluendo srl http://www.bluendo.com
jabber id: ff@jabber.bluendo.com
Re: counting rows in range queries
Posted by Paul Davis <pa...@gmail.com>.
On Thu, Jul 30, 2009 at 2:53 AM, Fabio Forno<fa...@gmail.com> wrote:
> On Thu, Jul 30, 2009 at 3:22 AM, Paul Davis<pa...@gmail.com> wrote:
>>>
>>
>> My bad, that should read 'emit([doc.bucket, doc.date], value)'
>>
>
> Well but it doesn't work either, since when I retrieve the view I get
> as many rows as the selected docs, with count 1. Perhaps my
> description was confusing, since date is not something like YYMMDD,
> but YYMMDDhhmmss. Therefore all the emitted keys are different and the
> reduce function can't do any aggregation.
>
This doesn't matter at all.
> This seems to me the case in which the returned total_rows should be
> not the number of docs in the view, but instead the actual number of
> selected documents. I know that there is a big penalty, however it is
> more efficient to let couchdb do it (if the btree implementation is is
> good, walking though keys shouldn't be so slow, since consecutive keys
> are in the same disk pages) than retrieving everything through the
> network. So why not adding a parameter to the view GET for telling
> couchdb if it has to count all the documents or not?
>
Total rows always reflects the number of rows in the view. It can
cause some confusion, but the other way around I don't know that it'd
cause any less.
Also, are you checking the value of the assumed single return row? If
you're not getting a single returned row then something is wrong. If
you are and it's wrong, then its wrong or I'm wrong. Either way, I'm
confused...
Paul
> --
> Fabio Forno, Ph.D.
> Bluendo srl http://www.bluendo.com
> jabber id: ff@jabber.bluendo.com
>
Re: counting rows in range queries
Posted by Fabio Forno <fa...@gmail.com>.
On Thu, Jul 30, 2009 at 3:22 AM, Paul Davis<pa...@gmail.com> wrote:
>>
>
> My bad, that should read 'emit([doc.bucket, doc.date], value)'
>
Well but it doesn't work either, since when I retrieve the view I get
as many rows as the selected docs, with count 1. Perhaps my
description was confusing, since date is not something like YYMMDD,
but YYMMDDhhmmss. Therefore all the emitted keys are different and the
reduce function can't do any aggregation.
This seems to me the case in which the returned total_rows should be
not the number of docs in the view, but instead the actual number of
selected documents. I know that there is a big penalty, however it is
more efficient to let couchdb do it (if the btree implementation is is
good, walking though keys shouldn't be so slow, since consecutive keys
are in the same disk pages) than retrieving everything through the
network. So why not adding a parameter to the view GET for telling
couchdb if it has to count all the documents or not?
--
Fabio Forno, Ph.D.
Bluendo srl http://www.bluendo.com
jabber id: ff@jabber.bluendo.com
Re: counting rows in range queries
Posted by Paul Davis <pa...@gmail.com>.
On Wed, Jul 29, 2009 at 9:16 PM, Fabio Forno<fa...@gmail.com> wrote:
> On Thu, Jul 30, 2009 at 2:48 AM, Paul Davis<pa...@gmail.com> wrote:
>>
>>
>> Does this not work?
>>
>> //map
>> function(doc)
>> {
>> emit(doc.bucket, doc.date);
>> }
>>
>> //reduce
>> function(keys, values, rereduce)
>> {
>> if(rereduce) return sum(values);
>> return values.length;
>> }
>>
>> // Number of rows
>> http://127.0.0.1:5984/db_name/_design/foo/_view/bar?startkey=[bucket,last_date_seen]&endkey=[bucket,blocker]
>>
>> // Actual rows
>> http://127.0.0.1:5984/db_name/_design/foo/_view/bar?startkey=[bucket,last_date_seen]&endkey[bucket,blocker]&reduce=false
>>
>> Or am I missing something?
>
> Perhaps I don't understand something in the map/reduce, but how can I
> define a range using the date if I emit just the bucket as key? In
> that way I count just the total number of items in the bucket, don't
> I?
>
> bye
>
> --
> Fabio Forno, Ph.D.
> Bluendo srl http://www.bluendo.com
> jabber id: ff@jabber.bluendo.com
>
My bad, that should read 'emit([doc.bucket, doc.date], value)'
Re: counting rows in range queries
Posted by Fabio Forno <fa...@gmail.com>.
On Thu, Jul 30, 2009 at 2:48 AM, Paul Davis<pa...@gmail.com> wrote:
>
>
> Does this not work?
>
> //map
> function(doc)
> {
> emit(doc.bucket, doc.date);
> }
>
> //reduce
> function(keys, values, rereduce)
> {
> if(rereduce) return sum(values);
> return values.length;
> }
>
> // Number of rows
> http://127.0.0.1:5984/db_name/_design/foo/_view/bar?startkey=[bucket,last_date_seen]&endkey=[bucket,blocker]
>
> // Actual rows
> http://127.0.0.1:5984/db_name/_design/foo/_view/bar?startkey=[bucket,last_date_seen]&endkey[bucket,blocker]&reduce=false
>
> Or am I missing something?
Perhaps I don't understand something in the map/reduce, but how can I
define a range using the date if I emit just the bucket as key? In
that way I count just the total number of items in the bucket, don't
I?
bye
--
Fabio Forno, Ph.D.
Bluendo srl http://www.bluendo.com
jabber id: ff@jabber.bluendo.com
Re: counting rows in range queries
Posted by Paul Davis <pa...@gmail.com>.
On Wed, Jul 29, 2009 at 8:38 PM, Fabio Forno<fa...@gmail.com> wrote:
> Hi,
> I've read many threads explaining how to count rows in view results,
> however I can't find any working solution for this particular case.
> I've a set of items divided in buckets, and each item has a posting
> date. I want to divide the items per bucket and sort them by their
> date, so I make a view with this key:
> [item.bucket, item.date]
>
> Now the difficult part. There are many consumers who retrieve the
> items from the buckets and each consumers knows the date of the last
> item it has retrieved, so I do a range query on the view with
> startkey=[item.bucket, last_date], endkey=[item.bucket, stopper]. This
> is fine, but if I want to tell the user how many items it has to
> retrieve before actually downloading them I can't do nothing but
> retrieving all the rows and start counting. Any idea for solving this
> with a proper map reduce, or some clever doc structure?
> The only possible solution I've found so far is storing marker
> documents for each possible consumer (containing just the item id) any
> time I post a new item in a bucket. These can be counted with a
> map/reduce and deleted when the items are retrieved, but I really
> don't like the solution since it requires too many writes.
>
> bye!
>
> --
> Fabio Forno, Ph.D.
> Bluendo srl http://www.bluendo.com
> jabber id: ff@jabber.bluendo.com
>
Does this not work?
//map
function(doc)
{
emit(doc.bucket, doc.date);
}
//reduce
function(keys, values, rereduce)
{
if(rereduce) return sum(values);
return values.length;
}
// Number of rows
http://127.0.0.1:5984/db_name/_design/foo/_view/bar?startkey=[bucket,last_date_seen]&endkey=[bucket,blocker]
// Actual rows
http://127.0.0.1:5984/db_name/_design/foo/_view/bar?startkey=[bucket,last_date_seen]&endkey[bucket,blocker]&reduce=false
Or am I missing something?
Paul Davis
Re: counting rows in range queries
Posted by Brian Candler <B....@pobox.com>.
On Thu, Jul 30, 2009 at 11:09:03AM +0200, Fabio Forno wrote:
> Well, after some iteration setting random parameters we figured it
> out! We need to append group=false and not true which is default
> behavior with ranges
Odd. Have you been trying all this out in Futon? It applies group=true for
you unless you say otherwise. I use curl at the command line for testing
views.
However, if your view has no reduce, or you've been giving reduce=false,
then I would have thought that group=true would have made no difference at
all (or would have been rejected).
It may be worth making a standalone test case and raising a ticket.
Regards,
Brian.
Re: counting rows in range queries
Posted by Fabio Forno <fa...@gmail.com>.
Well, after some iteration setting random parameters we figured it
out! We need to append group=false and not true which is default
behavior with ranges; this apparently triggers one additional reduce
loop when the view is accessed. And now I get a nice:
{"rows":[
{"key":null,"value":20}
]}
bye!
On Thu, Jul 30, 2009 at 10:58 AM, Fabio Forno<fa...@gmail.com> wrote:
> On Thu, Jul 30, 2009 at 9:58 AM, Brian Candler<B....@pobox.com> wrote:
>> On Thu, Jul 30, 2009 at 02:38:15AM +0200, Fabio Forno wrote:
>>> Now the difficult part. There are many consumers who retrieve the
>>> items from the buckets and each consumers knows the date of the last
>>> item it has retrieved, so I do a range query on the view with
>>> startkey=[item.bucket, last_date], endkey=[item.bucket, stopper]. This
>>> is fine, but if I want to tell the user how many items it has to
>>> retrieve before actually downloading them I can't do nothing but
>>> retrieving all the rows and start counting.
>>
>> I'm not sure I have understood the problem correctly, because:
>>
>> (1) if you do a view fetch with limit=0 in the query string, you'll get
>> the count without the documents.
>>
>
> In that case all I get is an empty row set in reduce views, and the
> total number of rows in the view, not the selection, while doing map
> views. However what you say is exactly what I'd like to have: when I
> write limit=0 I want couchdb to do a count for me, otherwise limit=0
> would be meaningless (instead with all the other limits or no limit
> the normal couchdb behavior is good)
--
Fabio Forno, Ph.D.
Bluendo srl http://www.bluendo.com
jabber id: ff@jabber.bluendo.com
Re: counting rows in range queries
Posted by Fabio Forno <fa...@gmail.com>.
On Thu, Jul 30, 2009 at 9:58 AM, Brian Candler<B....@pobox.com> wrote:
> On Thu, Jul 30, 2009 at 02:38:15AM +0200, Fabio Forno wrote:
>> Now the difficult part. There are many consumers who retrieve the
>> items from the buckets and each consumers knows the date of the last
>> item it has retrieved, so I do a range query on the view with
>> startkey=[item.bucket, last_date], endkey=[item.bucket, stopper]. This
>> is fine, but if I want to tell the user how many items it has to
>> retrieve before actually downloading them I can't do nothing but
>> retrieving all the rows and start counting.
>
> I'm not sure I have understood the problem correctly, because:
>
> (1) if you do a view fetch with limit=0 in the query string, you'll get
> the count without the documents.
>
In that case all I get is an empty row set in reduce views, and the
total number of rows in the view, not the selection, while doing map
views. However what you say is exactly what I'd like to have: when I
write limit=0 I want couchdb to do a count for me, otherwise limit=0
would be meaningless (instead with all the other limits or no limit
the normal couchdb behavior is good)
bye
--
Fabio Forno, Ph.D.
Bluendo srl http://www.bluendo.com
jabber id: ff@jabber.bluendo.com
Re: counting rows in range queries
Posted by Brian Candler <B....@pobox.com>.
On Thu, Jul 30, 2009 at 02:38:15AM +0200, Fabio Forno wrote:
> Now the difficult part. There are many consumers who retrieve the
> items from the buckets and each consumers knows the date of the last
> item it has retrieved, so I do a range query on the view with
> startkey=[item.bucket, last_date], endkey=[item.bucket, stopper]. This
> is fine, but if I want to tell the user how many items it has to
> retrieve before actually downloading them I can't do nothing but
> retrieving all the rows and start counting.
I'm not sure I have understood the problem correctly, because:
(1) if you do a view fetch with limit=0 in the query string, you'll get
the count without the documents.
(2) you can have a simple counter reduce function, and then do a reduce
across the range, which will give you the number of documents in that range.
function(ks, vs, co) {
if (co) {
return sum(vs);
} else {
return vs.length;
}
}
Indeed I think there's a built-in _count reduce function now, but I haven't
tried it myself.
> The only possible solution I've found so far is storing marker
> documents for each possible consumer (containing just the item id) any
> time I post a new item in a bucket. These can be counted with a
> map/reduce and deleted when the items are retrieved, but I really
> don't like the solution since it requires too many writes.
That's why I think I don't understand your problem. You said each consumer
already knows the date of the last item it has retrieved, so why would you
store it again?
Regards,
Brian.