couchdb-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Paola Lorusso <lorussopa...@yahoo.it>
Subject suggestion about how writing complex queries
Date Tue, 11 Nov 2008 21:44:30 GMT
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
Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message