suggestion about how writing complex queries

[Paola Lorusso <lo...@yahoo.it>]

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';
2) Get all posts commented by at least two different people;
3) Get all post after a specific date with more than one comment

                                                  
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

Re: suggestion about how writing complex queries

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

On Tue, Nov 11, 2008 at 4:44 PM, Paola Lorusso <lo...@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