incubator-couchdb-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Paul Davis" <paul.joseph.da...@gmail.com>
Subject Re: suggestion about how writing complex queries
Date Tue, 11 Nov 2008 22:25:34 GMT
On Tue, Nov 11, 2008 at 4:44 PM, Paola Lorusso <lorussopaola@yahoo.it> wrote:
> Hi,
> I am looking for creating a sample application on CouchDB like a blog application.
> I am tring to use views for quering the db
> I have decided to organise the database for three kinds of documents with these attributes:
>  POST
>  "type"=> "value",
>  "title"=> "value",
>  "content"=> "value",
>  "time"=> "value",
>  "author"=> value_ref_on_author
>
>  COMMENT
>  "type"=> "value",
>  "title"=> "value",
>  "content"=> "value",
>  "time"=> "value",
>  "author"=> value_ref_on_author,
>  "post" => value_ref_on_post
>
>  AUTHOR
>  "name"=> "value",
>  "email"=> "value",
>  "type"=> "value"
>
> I have no problem with sample queries, (for example: 'Take all comments for a specific
post'), but I have difficulties to create and test more complex queries. Particularly, how
can I do with these following queries?
>
> 1) Get all posts commented by 'Harriet';

# authors/by_name
function(doc)
{
   if(doc.type == 'author') emit(doc.name, doc._id);
}

author_id = http://127.0.0.1:5984/db_name/_view/authors/by_name?key="Harriet"

# posts/by_author_id
function(doc)
{
   if(doc.type == "comment") emit(doc.author, doc.post);
}

post_ids = http://127.0.0.1:5984/db_name/_view/posts/by_author?key=author_id
posts = POST {"keys": post_ids} to
http://127.0.0.1:5984/db_name/_all_docs?include_docs=True

Generally the first of the three requests would actually happen on a
previous page view. Ie, One page view lists authors, and clicking on
the user name loads a second page of posts. The multiple request for a
page is kinda hard to get over at first, but it really starts to make
sense after staring at this stuff for awhile.

> 2) Get all posts commented by at least two different people;
> 3) Get all post after a specific date with more than one comment

The only thing I can come up with here is either having a doc type
that tracks numbers of comments for posts or storing that field in the
original post. I'd be hesitant to store it on the post, but either way
there's still update conflict potential. Either way, once you have
access to the post/#comments mapping in a single doc, those should be
straight forward.

>
> In Mysql I wrote these in the following ways:
> 1) SELECT p.id,p.author, p.type,p.title,p.content,p.time FROM post p, comment c WHERE
p.id = c.post AND c.author = (SELECT a.id
> FROM author a WHERE a.name ='HARRIET' );
>
> 2) SELECT p.id FROM post p,comment c
>  WHERE c.post = p.id GROUP BY p.id
>  HAVING COUNT(DISTINCT(c.author)) >= 2;
>
> 3) SELECT p.id FROM post p, comment c WHERE p.time > '2008-08-10 00:00:00'
>  AND p.id = c.post GROUP BY p.id HAVING COUNT(c.id)> 1
>
> I hope someone can help me for these three cases.
> Thanks
>
> Paola
>
>
>      Unisciti alla community di Io fotografo e video, il nuovo corso di fotografia di
Gazzetta dello sport:
> http://www.flickr.com/groups/iofotografoevideo

HTH,
Paul

Mime
View raw message