need help for a query

[Patrick Aljord <pa...@gmail.com>]

Hey all,

I have a blog with many posts and many comments done by many users, a
comment has a post_id, a user_id and a created_at holding the creation
date of the comment.

I would like to query the 20 latest comments done only by user X, Y
and Z together, that is to say, if user X did the 20 latest comments
then it should return those 20 latest comments done by x only, but if
not, it should return for example something like 10 comments done by
X, 7 comments done by Y and 3 by Z, I hope you get the idea. And I
want them to be ordered by created_at.

I don't think there is a way to do so with one query only. What would
be the best way to do this query with couchdb?

Thanks in advance,

Pat

Re: need help for a query

[Paul Davis <pa...@gmail.com>]

On Fri, May 22, 2009 at 5:06 AM, Jan Lehnardt <ja...@apache.org> wrote:
>
> On 22 May 2009, at 02:13, Patrick Aljord wrote:
>
>> Thanks for you quick answer. Hm, what would max() do exactly?
>
> I think it is just the basic `list.max()` function, picking the member
> with the highest value from a list or set.
>

My bad. Should've been more specific. Jan's got it right though.

Actually now that I think about it though, you shouldn't need that.
Just get the last 20 comments per user and then merge them client
side. Though I just woke up and I haven't had my coffee so I could be
misremembering the original question.

> Cheers
> Jan
> --
>
>

Re: need help for a query

[Jan Lehnardt <ja...@apache.org>]

On 22 May 2009, at 02:13, Patrick Aljord wrote:

> Thanks for you quick answer. Hm, what would max() do exactly?

I think it is just the basic `list.max()` function, picking the member
with the highest value from a list or set.

Cheers
Jan
--

Re: need help for a query

[Patrick Aljord <pa...@gmail.com>]

Thanks for you quick answer. Hm, what would max() do exactly?

Re: need help for a query

[Adam Wolff <aw...@gmail.com>]

The alternative is to put the posts file with an updated time every time you
add a comment. As long as you *always* update the time every time you put
the posts document, you don't have to worry about contention -- if an update
of the updated time fails, you just ignore it.
http://mail-archives.apache.org/mod_mbox/couchdb-user/200904.mbox/%3ce8d26ac40904160921l6494cb5bp411e1ab01d1d9d1f@mail.gmail.com%3e

A

On Thu, May 21, 2009 at 4:57 PM, Paul Davis <pa...@gmail.com>wrote:

> I don't think you can do that in 1 query because you have two
> unrelated orderings. Remember, don't be too concerned with multiple
> queries to the database.
>
> The layout I'd use is this:
>
> bar View:
> Map: emit(doc.user_id, doc.created_at)
> Reduce: return max(values); // You'll have to write max
>
> zing View 2:
> Map: emit([doc.user_id, doc.created_at], doc.post_id)
>
> Query strategy:
> Multi-get the bar view with your user id's of interest.
> top20 = []
> For each user:
>    get last 20 posts for user
>    merge last 20 into top20
> profit()
>
> There could be something more clever but nothing is coming right now.
>
> HTH,
> Paul Davis
>
> On Thu, May 21, 2009 at 7:39 PM, Patrick Aljord <pa...@gmail.com> wrote:
> > Hey all,
> >
> > I have a blog with many posts and many comments done by many users, a
> > comment has a post_id, a user_id and a created_at holding the creation
> > date of the comment.
> >
> > I would like to query the 20 latest comments done only by user X, Y
> > and Z together, that is to say, if user X did the 20 latest comments
> > then it should return those 20 latest comments done by x only, but if
> > not, it should return for example something like 10 comments done by
> > X, 7 comments done by Y and 3 by Z, I hope you get the idea. And I
> > want them to be ordered by created_at.
> >
> > I don't think there is a way to do so with one query only. What would
> > be the best way to do this query with couchdb?
> >
> > Thanks in advance,
> >
> > Pat
> >
>

Re: need help for a query

[Paul Davis <pa...@gmail.com>]

I don't think you can do that in 1 query because you have two
unrelated orderings. Remember, don't be too concerned with multiple
queries to the database.

The layout I'd use is this:

bar View:
Map: emit(doc.user_id, doc.created_at)
Reduce: return max(values); // You'll have to write max

zing View 2:
Map: emit([doc.user_id, doc.created_at], doc.post_id)

Query strategy:
Multi-get the bar view with your user id's of interest.
top20 = []
For each user:
    get last 20 posts for user
    merge last 20 into top20
profit()

There could be something more clever but nothing is coming right now.

HTH,
Paul Davis

On Thu, May 21, 2009 at 7:39 PM, Patrick Aljord <pa...@gmail.com> wrote:
> Hey all,
>
> I have a blog with many posts and many comments done by many users, a
> comment has a post_id, a user_id and a created_at holding the creation
> date of the comment.
>
> I would like to query the 20 latest comments done only by user X, Y
> and Z together, that is to say, if user X did the 20 latest comments
> then it should return those 20 latest comments done by x only, but if
> not, it should return for example something like 10 comments done by
> X, 7 comments done by Y and 3 by Z, I hope you get the idea. And I
> want them to be ordered by created_at.
>
> I don't think there is a way to do so with one query only. What would
> be the best way to do this query with couchdb?
>
> Thanks in advance,
>
> Pat
>