You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@couchdb.apache.org by Anand Chitipothu <an...@gmail.com> on 2009/03/13 12:59:57 UTC
equivalent of JOINs in couchdb?
Hi,
I have been reading about couchdb and trying understand how it works.
One thing that i'm unable to understand is how to do equivalent of
JOINs in couchdb.
Let me explain what I need with an example.
I want to store books in couchdb with each book having title, authors,
isbn, lccn, publisher and many other fields.
I would like to be able to query the system for any combination of
these. some thing like:
type="book" and publisher="foo" and author="bar
type="book" and isbn="1234567890"
How to do this in couchdb?
I wouldn't want to create a temporary view for running each query as
it is very expensive.
Thanks,
Anand
Re: equivalent of JOINs in couchdb?
Posted by Manolo Padron Martinez <ma...@gmail.com>.
> Hi,
Hi
> type="book" and publisher="foo" and author="bar
As I know this should be done with a view that makes a complex key (I
mean [type,publisher,author]).
But I'm still a newbie...
Regards from Canary Islands
Manuel Padrón Martínez
Re: equivalent of JOINs in couchdb?
Posted by Daniel Friesen <da...@telus.net>.
Don't take my word completely for it, since I only started looking into
CouchDB earlier on today.
But I think the issue is understanding the difference in thought process.
Rather than querying for data joining that data together, you index data
with keys beforehand and use those keys to find the data.
For any of this kind of complex data, you create a view that emits keys
using the relevant data.
So rather than a JOIN which finds all comments by post ids which are
found by issue ids. You create a view which emits keys referring to the
issue by tracking backwards to find the issue out from the post. Then
you can just give it a issue id and it'll list out the comments.
~Daniel Friesen (Dantman, Nadir-Seen-Fire)
Anand Chitipothu wrote:
>> http://www.cmlenz.net/archives/2007/10/couchdb-joins
>>
>>
>
> Thanks. That was a good article.
>
> For my understanding, let me extend the same example to a magazine
> instead of blog.
> A magazine will have issues, each issue will have posts and post will
> have comments.
> I can use the technique described in the above blog post to display
> comments in each post.
>
> What if I want to display all comments of all posts of an issue?
>
Re: equivalent of JOINs in couchdb?
Posted by Dean Landolt <de...@deanlandolt.com>.
On Tue, Mar 17, 2009 at 6:59 AM, Jan Lehnardt <ja...@apache.org> wrote:
>
> On 17 Mar 2009, at 10:37, Anand Chitipothu wrote:
>
> On Tue, Mar 17, 2009 at 12:38 PM, Wout Mertens <wo...@gmail.com>
>> wrote:
>>
>>> On Mar 17, 2009, at 7:34 AM, Anand Chitipothu wrote:
>>>
>>> http://www.cmlenz.net/archives/2007/10/couchdb-joins
>>>>>
>>>>>
>>>> Thanks. That was a good article.
>>>>
>>>> For my understanding, let me extend the same example to a magazine
>>>> instead of blog.
>>>> A magazine will have issues, each issue will have posts and post will
>>>> have comments.
>>>> I can use the technique described in the above blog post to display
>>>> comments in each post.
>>>>
>>>> What if I want to display all comments of all posts of an issue?
>>>>
>>>
>>>
>>> Something like
>>>
>>> function(doc) {
>>> if (doc.type == "post") {
>>> map([doc.magazine, doc._id, 1], doc);
>>> } else if (doc.type == "comment") {
>>> map([doc.magazine, doc.post, 2], doc);
>>> }
>>> }
>>>
>>> ? You'd need to duplicate the magazine information on the comments
>>> though.
>>>
>>
>> Is it not possible to achieve that without adding magazine to each
>> comment?
>>
>
> Not really, no. Is it an issue?
Probably not for the magazine example, but in many of cases (particularly
where the equivalent of "magazine issue" is subject to frequent change),
yes.
Of course, it's an issue easily solved by alternative view indexing. I think
couchdb-lucene can handle this just fine as is (I haven't played with it so
I can't confirm). I know there are a lot of *sqlisms* couch shouldn't
support out of the box, but after exploring JSONQuery a little further, I
can't wait for the day when I can run a JSONQuery expressions against a view
(even if it's just all_docs) in O(log n) time.
>From an HCI standpoint, I'm convinced that the most useful generic mode of
interaction with a data set is the good ol' sortable, filterable data grid.
Compound sorts and filters will never be possible on large data sets in
couch without an alternative indexing model. That's not to say the current
model isn't broadly useful (and amazingly scalable) -- but I will say there
are certain (common) classes of problem it'll never solve on its own.
Re: equivalent of JOINs in couchdb?
Posted by Jan Lehnardt <ja...@apache.org>.
On 17 Mar 2009, at 10:37, Anand Chitipothu wrote:
> On Tue, Mar 17, 2009 at 12:38 PM, Wout Mertens
> <wo...@gmail.com> wrote:
>> On Mar 17, 2009, at 7:34 AM, Anand Chitipothu wrote:
>>
>>>> http://www.cmlenz.net/archives/2007/10/couchdb-joins
>>>>
>>>
>>> Thanks. That was a good article.
>>>
>>> For my understanding, let me extend the same example to a magazine
>>> instead of blog.
>>> A magazine will have issues, each issue will have posts and post
>>> will
>>> have comments.
>>> I can use the technique described in the above blog post to display
>>> comments in each post.
>>>
>>> What if I want to display all comments of all posts of an issue?
>>
>>
>> Something like
>>
>> function(doc) {
>> if (doc.type == "post") {
>> map([doc.magazine, doc._id, 1], doc);
>> } else if (doc.type == "comment") {
>> map([doc.magazine, doc.post, 2], doc);
>> }
>> }
>>
>> ? You'd need to duplicate the magazine information on the comments
>> though.
>
> Is it not possible to achieve that without adding magazine to each
> comment?
Not really, no. Is it an issue?
Cheers
Jan
--
Re: equivalent of JOINs in couchdb?
Posted by Anand Chitipothu <an...@gmail.com>.
On Tue, Mar 17, 2009 at 12:38 PM, Wout Mertens <wo...@gmail.com> wrote:
> On Mar 17, 2009, at 7:34 AM, Anand Chitipothu wrote:
>
>>> http://www.cmlenz.net/archives/2007/10/couchdb-joins
>>>
>>
>> Thanks. That was a good article.
>>
>> For my understanding, let me extend the same example to a magazine
>> instead of blog.
>> A magazine will have issues, each issue will have posts and post will
>> have comments.
>> I can use the technique described in the above blog post to display
>> comments in each post.
>>
>> What if I want to display all comments of all posts of an issue?
>
>
> Something like
>
> function(doc) {
> if (doc.type == "post") {
> map([doc.magazine, doc._id, 1], doc);
> } else if (doc.type == "comment") {
> map([doc.magazine, doc.post, 2], doc);
> }
> }
>
> ? You'd need to duplicate the magazine information on the comments though.
Is it not possible to achieve that without adding magazine to each comment?
Re: equivalent of JOINs in couchdb?
Posted by Wout Mertens <wo...@gmail.com>.
On Mar 17, 2009, at 7:34 AM, Anand Chitipothu wrote:
>> http://www.cmlenz.net/archives/2007/10/couchdb-joins
>>
>
> Thanks. That was a good article.
>
> For my understanding, let me extend the same example to a magazine
> instead of blog.
> A magazine will have issues, each issue will have posts and post will
> have comments.
> I can use the technique described in the above blog post to display
> comments in each post.
>
> What if I want to display all comments of all posts of an issue?
Something like
function(doc) {
if (doc.type == "post") {
map([doc.magazine, doc._id, 1], doc);
} else if (doc.type == "comment") {
map([doc.magazine, doc.post, 2], doc);
}
}
? You'd need to duplicate the magazine information on the comments
though.
Wout.
Re: equivalent of JOINs in couchdb?
Posted by Anand Chitipothu <an...@gmail.com>.
> http://www.cmlenz.net/archives/2007/10/couchdb-joins
>
Thanks. That was a good article.
For my understanding, let me extend the same example to a magazine
instead of blog.
A magazine will have issues, each issue will have posts and post will
have comments.
I can use the technique described in the above blog post to display
comments in each post.
What if I want to display all comments of all posts of an issue?
Re: equivalent of JOINs in couchdb?
Posted by Noah Slater <ns...@apache.org>.
On Sun, Mar 15, 2009 at 07:16:47PM +0700, Jason Smith wrote:
> (Also, sorry to post across threads, but ISBN is most certainly *not
> necessarily unique* per book! It may be unique for a title (i.e. some
> text by some author distributed by some publisher), but of course all
> printings of that title have the same ISBN. For example, a book
> inventory application may opt to use one document per book, thereby
> having several documents with the same ISBN.)
Not necessarily, second editions of the same book MAY have a different ISBN.
--
Noah Slater, http://tumbolia.org/nslater
Re: equivalent of JOINs in couchdb?
Posted by Jason Smith <jh...@proven-corporation.com>.
Jan Lehnardt wrote:
>> I want to store books in couchdb with each book having title, authors,
>> isbn, lccn, publisher and many other fields.
>> I would like to be able to query the system for any combination of
>> these. some thing like:
>>
>> type="book" and publisher="foo" and author="bar
>> type="book" and isbn="1234567890"
>>
>> How to do this in couchdb?
>
> http://www.cmlenz.net/archives/2007/10/couchdb-joins
I would also suggest looking at the couchdb-lucene project:
http://github.com/rnewson/couchdb-lucene/tree/master
I found that it's pretty easy to get up and running, and it seems to
index your documents based on their top-level keys. So your example of
searching "type:book publisher:foo author:bar" might fit quite well.
(Also, sorry to post across threads, but ISBN is most certainly *not
necessarily unique* per book! It may be unique for a title (i.e. some
text by some author distributed by some publisher), but of course all
printings of that title have the same ISBN. For example, a book
inventory application may opt to use one document per book, thereby
having several documents with the same ISBN.)
--
Jason Smith
Proven Corporation
Bangkok, Thailand
http://www.proven-corporation.com
Re: equivalent of JOINs in couchdb?
Posted by Jan Lehnardt <ja...@apache.org>.
On 13 Mar 2009, at 12:59, Anand Chitipothu wrote:
> Hi,
>
> I have been reading about couchdb and trying understand how it works.
> One thing that i'm unable to understand is how to do equivalent of
> JOINs in couchdb.
>
> Let me explain what I need with an example.
> I want to store books in couchdb with each book having title, authors,
> isbn, lccn, publisher and many other fields.
> I would like to be able to query the system for any combination of
> these. some thing like:
>
> type="book" and publisher="foo" and author="bar
> type="book" and isbn="1234567890"
>
> How to do this in couchdb?
http://www.cmlenz.net/archives/2007/10/couchdb-joins
Cheers
Jan
--
Re: equivalent of JOINs in couchdb?
Posted by Adam Groves <ad...@gmail.com>.
Hi Anand,
Try this
View:
function(doc) {
emit([doc['type'], doc['publisher'], doc['author']] null);
}
Query:
../view_name?key=["book", "penguin", "Rowling"]
Regards
Adam
2009/3/13 Anand Chitipothu <an...@gmail.com>:
> Hi,
>
> I have been reading about couchdb and trying understand how it works.
> One thing that i'm unable to understand is how to do equivalent of
> JOINs in couchdb.
>
> Let me explain what I need with an example.
> I want to store books in couchdb with each book having title, authors,
> isbn, lccn, publisher and many other fields.
> I would like to be able to query the system for any combination of
> these. some thing like:
>
> type="book" and publisher="foo" and author="bar
> type="book" and isbn="1234567890"
>
> How to do this in couchdb?
> I wouldn't want to create a temporary view for running each query as
> it is very expensive.
>
> Thanks,
> Anand
>
Re: equivalent of JOINs in couchdb?
Posted by Wout Mertens <wo...@gmail.com>.
On Mar 13, 2009, at 12:59 PM, Anand Chitipothu wrote:
> I have been reading about couchdb and trying understand how it works.
> One thing that i'm unable to understand is how to do equivalent of
> JOINs in couchdb.
>
> Let me explain what I need with an example.
> I want to store books in couchdb with each book having title, authors,
> isbn, lccn, publisher and many other fields.
> I would like to be able to query the system for any combination of
> these. some thing like:
>
> type="book" and publisher="foo" and author="bar
> type="book" and isbn="1234567890"
>
> How to do this in couchdb?
To begin with, since ISBN is supposed to be unique per book, you can
make that be the DocID for the documents. That way you can simply look
for a certain ISBN number and get an answer instantly.
Then you need to look at your application and see which view indexes
you need. Each index (created by map()) allows you to search for the
indexed fields at the expense of diskspace. You can use complex keys
like [doc.date,doc.author] to find books sorted by date and then by
author. To find anything released since a certain date you would
specify a startkey of ["<date>",""].
Your application then has to filter out the results further, so it
pays to look for the fields that you will be sorting on and that will
divide the search space in the smallest blocks.
Note that it looks like you will do a lot of text searches. Have a
look at http://github.com/rnewson/couchdb-lucene/tree/master which is
better suited for that.
> I wouldn't want to create a temporary view for running each query as
> it is very expensive.
You're right, it is. Only use those for development.
Wout.