You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@couchdb.apache.org by Simon Woodhead <si...@simwood.com> on 2010/09/17 15:04:03 UTC
Conditional joins
Hi folks,
I'm pretty green with views and have a need to do a join with results
from one side returned if a condition is met on the other. In SQL I'd
do:
select * from child join parent on child.id=parent.id where parent.status=1;
I've read the 'join' documentation (e.g.
http://www.cmlenz.net/archives/2007/10/couchdb-joins) and can return
documents of multiple types with matching keys ok. What I can't do is
do the same but only if the parent matches a condition.
Specifically, my data is like:
{"45a6951b34a6f45bd44fd169c0003f9c","_rev":"1-6c88818b43d562c45e452bf00db06ee2","type":"domain","domain":"test.com","status":1},
{"_id":"45a6951b34a6f45bd44fd169c00046db","_rev":"1-37eaf0c4944dac9f5fe07b25c0b68b1d","type":"domain","domain":"test2.com","status":0},
{"_id":"45a6951b34a6f45bd44fd169c00053d9","_rev":"3-729f46c86ee4984941939c65787126fb","type":"user","domain":"test.com","user":"foo","password":"test123"},
{"_id":"45a6951b34a6f45bd44fd169c00060c8","_rev":"1-46997eba12e7adbb6e9266a1fe83c42d","type":"user","domain":"test2.com","user":"bar","password":"test123"}
I want to be be able to query a view by domain and see all users for
it if the status is 1. So in this case I'd see users for test.com but
not for test2.com.
Any help would be appreciated.
Thanks!
Simon
--
***** Email confidentiality notice *****
This message is private and confidential. If you have received this message in error, please notify us and remove it from your system.
Simwood eSMS Limited is a limited company registered in England and Wales. Registered number: 03379831. Registered office: c/o HW Chartered Accountants, Keepers Lane, The Wergs, Wolverhampton, WV6 8UA. Trading address: Falcon Drive, Cardiff Bay, Cardiff, CF10 4RU.
Re: Conditional joins
Posted by Patrick Barnes <mr...@gmail.com>.
The basic tenets of views are:
1. Map/reduce functions must be idempotent, and look only at the
document/data passed to it.
2. Emitted keys get sorted onto a single axis, and with view queries you
can retrieve a single given key, multiple given keys, or a contiguous
subset of keys with a given start and end.
For other scenarios where a link between documents needs to be displayed
and a subset of documents retrieved (ie for a one->many relationship
like posts->comments), both the independent and dependent documents have
to emit a key that can place them together appropriately in the view
axis. (so a post could emit [doc._id, 0] and a comment could emit
[doc.post_id, 1])
The fundamental issue with your scenario is tenet #1 - there's normally
no way for a 'child' to know what the status of the 'parent' is. This
also causes problems for tenet #2, because if you want to emit for
instance the status in the key, #1 means that the 'child' can't emit
that same key to be adjacent to the parent. (it won't know what to emit)
Basically, I can think of two ways to solve this:
A. Have the necessary fields of the 'parent' be stored also in the
dependent 'child' - i.e. doc.parent_status. Then you can make a view
that either only shows active docs, or is keyed like eg [status,
parent_id, 0|1]
This approach works if you have large numbers of docs that might be need
to be returned at a time, but the docs don't change that attribute very
often. (because any status update to the parent needs to be cascaded to
children)
B. Use two requests like Wout suggested - In the first request fetch the
parents. (out of a view that allows you to select by status as you need)
In the second request, fetch the children of those parents. (on a second
view, using multiple keys).
This does not need extra data in the view, but if there are too many
keys requested in the second view it can be inefficient.
B is less hassle, unless you have large numbers of docs read at once,
and don't change the docs very often.
HTH,
-Patrick
On 17/09/2010 11:04 PM, Simon Woodhead wrote:
> Hi folks,
>
> I'm pretty green with views and have a need to do a join with results
> from one side returned if a condition is met on the other. In SQL I'd
> do:
>
> select * from child join parent on child.id=parent.id where parent.status=1;
>
> I've read the 'join' documentation (e.g.
> http://www.cmlenz.net/archives/2007/10/couchdb-joins) and can return
> documents of multiple types with matching keys ok. What I can't do is
> do the same but only if the parent matches a condition.
>
> Specifically, my data is like:
>
> {"45a6951b34a6f45bd44fd169c0003f9c","_rev":"1-6c88818b43d562c45e452bf00db06ee2","type":"domain","domain":"test.com","status":1},
> {"_id":"45a6951b34a6f45bd44fd169c00046db","_rev":"1-37eaf0c4944dac9f5fe07b25c0b68b1d","type":"domain","domain":"test2.com","status":0},
> {"_id":"45a6951b34a6f45bd44fd169c00053d9","_rev":"3-729f46c86ee4984941939c65787126fb","type":"user","domain":"test.com","user":"foo","password":"test123"},
> {"_id":"45a6951b34a6f45bd44fd169c00060c8","_rev":"1-46997eba12e7adbb6e9266a1fe83c42d","type":"user","domain":"test2.com","user":"bar","password":"test123"}
>
> I want to be be able to query a view by domain and see all users for
> it if the status is 1. So in this case I'd see users for test.com but
> not for test2.com.
> Any help would be appreciated.
>
> Thanks!
> Simon
> --
> ***** Email confidentiality notice *****
>
> This message is private and confidential. If you have received this message in error, please notify us and remove it from your system.
>
>
> Simwood eSMS Limited is a limited company registered in England and Wales. Registered number: 03379831. Registered office: c/o HW Chartered Accountants, Keepers Lane, The Wergs, Wolverhampton, WV6 8UA. Trading address: Falcon Drive, Cardiff Bay, Cardiff, CF10 4RU.
>
>
>
Re: Conditional joins
Posted by Wout Mertens <wo...@gmail.com>.
Hi Simon,
You're trying to apply information from one document onto all your documents. This means you'll need to do two queries, one for each information extraction.
You need a view with the domains only showing per status:
function(doc) { doc.type=="domain" && doc.status == 1 && emit (doc.domain, null); }
Then you need a view with the users by domain:
function(doc) { doc.type=="user" && emit (doc.domain, doc); }
So you query the first view for all active domains, and then you query the second view with those domains as the keys.
Wout.
On Sep 17, 2010, at 15:04 , Simon Woodhead wrote:
> Hi folks,
>
> I'm pretty green with views and have a need to do a join with results
> from one side returned if a condition is met on the other. In SQL I'd
> do:
>
> select * from child join parent on child.id=parent.id where parent.status=1;
>
> I've read the 'join' documentation (e.g.
> http://www.cmlenz.net/archives/2007/10/couchdb-joins) and can return
> documents of multiple types with matching keys ok. What I can't do is
> do the same but only if the parent matches a condition.
>
> Specifically, my data is like:
>
> {"45a6951b34a6f45bd44fd169c0003f9c","_rev":"1-6c88818b43d562c45e452bf00db06ee2","type":"domain","domain":"test.com","status":1},
> {"_id":"45a6951b34a6f45bd44fd169c00046db","_rev":"1-37eaf0c4944dac9f5fe07b25c0b68b1d","type":"domain","domain":"test2.com","status":0},
> {"_id":"45a6951b34a6f45bd44fd169c00053d9","_rev":"3-729f46c86ee4984941939c65787126fb","type":"user","domain":"test.com","user":"foo","password":"test123"},
> {"_id":"45a6951b34a6f45bd44fd169c00060c8","_rev":"1-46997eba12e7adbb6e9266a1fe83c42d","type":"user","domain":"test2.com","user":"bar","password":"test123"}
>
> I want to be be able to query a view by domain and see all users for
> it if the status is 1. So in this case I'd see users for test.com but
> not for test2.com.
> Any help would be appreciated.
>
> Thanks!
> Simon
> --
> ***** Email confidentiality notice *****
>
> This message is private and confidential. If you have received this message in error, please notify us and remove it from your system.
>
>
> Simwood eSMS Limited is a limited company registered in England and Wales. Registered number: 03379831. Registered office: c/o HW Chartered Accountants, Keepers Lane, The Wergs, Wolverhampton, WV6 8UA. Trading address: Falcon Drive, Cardiff Bay, Cardiff, CF10 4RU.
>
>
Re: Conditional joins
Posted by Norman Barker <no...@gmail.com>.
Sounds like using two views and a multiview will work (this could be
one view with complex keys - a megaview).
http://github.com/normanb/couchdb/blob/trunk/README.multiview
Norman
On Fri, Sep 17, 2010 at 8:08 AM, Kenneth Tyler <ke...@8thfold.com> wrote:
> Sorry,
> I missed an important part of your question.
> Two views would work. It might be that building a composite key would also
> work, since you have the domain information in all the user documents.
> function(doc){if(doc.type=='user'){emit([doc.domain,doc.status],doc)}
>
> and then query the view looking for [test.com,1]
>
> ken tyler
>
> On Fri, Sep 17, 2010 at 6:56 AM, Kenneth Tyler <ke...@8thfold.com> wrote:
>
>> Simon,
>> I think this is a case where the flat document structure of couch documents
>> makes things much simpler than in SQL.
>>
>> If you had a view like this:
>> function(doc){if(doc.type=='user'){emit(doc.status,doc)}
>> then it would return all the user documents with their status as their key
>> and you could use ?key="1" (i think the quotes are necessary) to get all
>> the users whose status is 1
>>
>> if you want to do the same thing for domains, you just write a "domain"
>> view that returns status as the key
>>
>> ken tyler
>>
>>
>> On Fri, Sep 17, 2010 at 6:04 AM, Simon Woodhead <
>> simon.woodhead@simwood.com> wrote:
>>
>>> Hi folks,
>>>
>>> I'm pretty green with views and have a need to do a join with results
>>> from one side returned if a condition is met on the other. In SQL I'd
>>> do:
>>>
>>> select * from child join parent on child.id=parent.id where
>>> parent.status=1;
>>>
>>> I've read the 'join' documentation (e.g.
>>> http://www.cmlenz.net/archives/2007/10/couchdb-joins) and can return
>>> documents of multiple types with matching keys ok. What I can't do is
>>> do the same but only if the parent matches a condition.
>>>
>>> Specifically, my data is like:
>>>
>>>
>>> {"45a6951b34a6f45bd44fd169c0003f9c","_rev":"1-6c88818b43d562c45e452bf00db06ee2","type":"domain","domain":"
>>> test.com","status":1},
>>>
>>> {"_id":"45a6951b34a6f45bd44fd169c00046db","_rev":"1-37eaf0c4944dac9f5fe07b25c0b68b1d","type":"domain","domain":"
>>> test2.com","status":0},
>>>
>>> {"_id":"45a6951b34a6f45bd44fd169c00053d9","_rev":"3-729f46c86ee4984941939c65787126fb","type":"user","domain":"
>>> test.com","user":"foo","password":"test123"},
>>>
>>> {"_id":"45a6951b34a6f45bd44fd169c00060c8","_rev":"1-46997eba12e7adbb6e9266a1fe83c42d","type":"user","domain":"
>>> test2.com","user":"bar","password":"test123"}
>>>
>>> I want to be be able to query a view by domain and see all users for
>>> it if the status is 1. So in this case I'd see users for test.com but
>>> not for test2.com.
>>> Any help would be appreciated.
>>>
>>> Thanks!
>>> Simon
>>> --
>>> ***** Email confidentiality notice *****
>>>
>>> This message is private and confidential. If you have received this
>>> message in error, please notify us and remove it from your system.
>>>
>>>
>>> Simwood eSMS Limited is a limited company registered in England and Wales.
>>> Registered number: 03379831. Registered office: c/o HW Chartered
>>> Accountants, Keepers Lane, The Wergs, Wolverhampton, WV6 8UA. Trading
>>> address: Falcon Drive, Cardiff Bay, Cardiff, CF10 4RU.
>>>
>>>
>>>
>>
>
Re: Conditional joins
Posted by Simon Woodhead <si...@simwood.com>.
Thanks for the great comments guys. I think I'll run with the two view
approach.
cheers,
Simon
On Fri, Sep 17, 2010 at 3:08 PM, Kenneth Tyler <ke...@8thfold.com> wrote:
> Sorry,
> I missed an important part of your question.
> Two views would work. It might be that building a composite key would also
> work, since you have the domain information in all the user documents.
> function(doc){if(doc.type=='user'){emit([doc.domain,doc.status],doc)}
>
> and then query the view looking for [test.com,1]
>
> ken tyler
>
> On Fri, Sep 17, 2010 at 6:56 AM, Kenneth Tyler <ke...@8thfold.com> wrote:
>
> > Simon,
> > I think this is a case where the flat document structure of couch
> documents
> > makes things much simpler than in SQL.
> >
> > If you had a view like this:
> > function(doc){if(doc.type=='user'){emit(doc.status,doc)}
> > then it would return all the user documents with their status as their
> key
> > and you could use ?key="1" (i think the quotes are necessary) to get all
> > the users whose status is 1
> >
> > if you want to do the same thing for domains, you just write a "domain"
> > view that returns status as the key
> >
> > ken tyler
> >
> >
> > On Fri, Sep 17, 2010 at 6:04 AM, Simon Woodhead <
> > simon.woodhead@simwood.com> wrote:
> >
> >> Hi folks,
> >>
> >> I'm pretty green with views and have a need to do a join with results
> >> from one side returned if a condition is met on the other. In SQL I'd
> >> do:
> >>
> >> select * from child join parent on child.id=parent.id where
> >> parent.status=1;
> >>
> >> I've read the 'join' documentation (e.g.
> >> http://www.cmlenz.net/archives/2007/10/couchdb-joins) and can return
> >> documents of multiple types with matching keys ok. What I can't do is
> >> do the same but only if the parent matches a condition.
> >>
> >> Specifically, my data is like:
> >>
> >>
> >>
> {"45a6951b34a6f45bd44fd169c0003f9c","_rev":"1-6c88818b43d562c45e452bf00db06ee2","type":"domain","domain":"
> >> test.com","status":1},
> >>
> >>
> {"_id":"45a6951b34a6f45bd44fd169c00046db","_rev":"1-37eaf0c4944dac9f5fe07b25c0b68b1d","type":"domain","domain":"
> >> test2.com","status":0},
> >>
> >>
> {"_id":"45a6951b34a6f45bd44fd169c00053d9","_rev":"3-729f46c86ee4984941939c65787126fb","type":"user","domain":"
> >> test.com","user":"foo","password":"test123"},
> >>
> >>
> {"_id":"45a6951b34a6f45bd44fd169c00060c8","_rev":"1-46997eba12e7adbb6e9266a1fe83c42d","type":"user","domain":"
> >> test2.com","user":"bar","password":"test123"}
> >>
> >> I want to be be able to query a view by domain and see all users for
> >> it if the status is 1. So in this case I'd see users for test.com but
> >> not for test2.com.
> >> Any help would be appreciated.
> >>
> >> Thanks!
> >> Simon
> >> --
> >> ***** Email confidentiality notice *****
> >>
> >> This message is private and confidential. If you have received this
> >> message in error, please notify us and remove it from your system.
> >>
> >>
> >> Simwood eSMS Limited is a limited company registered in England and
> Wales.
> >> Registered number: 03379831. Registered office: c/o HW Chartered
> >> Accountants, Keepers Lane, The Wergs, Wolverhampton, WV6 8UA. Trading
> >> address: Falcon Drive, Cardiff Bay, Cardiff, CF10 4RU.
> >>
> >>
> >>
> >
>
--
***** Email confidentiality notice *****
This message is private and confidential. If you have received this message in error, please notify us and remove it from your system.
Simwood eSMS Limited is a limited company registered in England and Wales. Registered number: 03379831. Registered office: c/o HW Chartered Accountants, Keepers Lane, The Wergs, Wolverhampton, WV6 8UA. Trading address: Falcon Drive, Cardiff Bay, Cardiff, CF10 4RU.
Re: Conditional joins
Posted by Kenneth Tyler <ke...@8thfold.com>.
Sorry,
I missed an important part of your question.
Two views would work. It might be that building a composite key would also
work, since you have the domain information in all the user documents.
function(doc){if(doc.type=='user'){emit([doc.domain,doc.status],doc)}
and then query the view looking for [test.com,1]
ken tyler
On Fri, Sep 17, 2010 at 6:56 AM, Kenneth Tyler <ke...@8thfold.com> wrote:
> Simon,
> I think this is a case where the flat document structure of couch documents
> makes things much simpler than in SQL.
>
> If you had a view like this:
> function(doc){if(doc.type=='user'){emit(doc.status,doc)}
> then it would return all the user documents with their status as their key
> and you could use ?key="1" (i think the quotes are necessary) to get all
> the users whose status is 1
>
> if you want to do the same thing for domains, you just write a "domain"
> view that returns status as the key
>
> ken tyler
>
>
> On Fri, Sep 17, 2010 at 6:04 AM, Simon Woodhead <
> simon.woodhead@simwood.com> wrote:
>
>> Hi folks,
>>
>> I'm pretty green with views and have a need to do a join with results
>> from one side returned if a condition is met on the other. In SQL I'd
>> do:
>>
>> select * from child join parent on child.id=parent.id where
>> parent.status=1;
>>
>> I've read the 'join' documentation (e.g.
>> http://www.cmlenz.net/archives/2007/10/couchdb-joins) and can return
>> documents of multiple types with matching keys ok. What I can't do is
>> do the same but only if the parent matches a condition.
>>
>> Specifically, my data is like:
>>
>>
>> {"45a6951b34a6f45bd44fd169c0003f9c","_rev":"1-6c88818b43d562c45e452bf00db06ee2","type":"domain","domain":"
>> test.com","status":1},
>>
>> {"_id":"45a6951b34a6f45bd44fd169c00046db","_rev":"1-37eaf0c4944dac9f5fe07b25c0b68b1d","type":"domain","domain":"
>> test2.com","status":0},
>>
>> {"_id":"45a6951b34a6f45bd44fd169c00053d9","_rev":"3-729f46c86ee4984941939c65787126fb","type":"user","domain":"
>> test.com","user":"foo","password":"test123"},
>>
>> {"_id":"45a6951b34a6f45bd44fd169c00060c8","_rev":"1-46997eba12e7adbb6e9266a1fe83c42d","type":"user","domain":"
>> test2.com","user":"bar","password":"test123"}
>>
>> I want to be be able to query a view by domain and see all users for
>> it if the status is 1. So in this case I'd see users for test.com but
>> not for test2.com.
>> Any help would be appreciated.
>>
>> Thanks!
>> Simon
>> --
>> ***** Email confidentiality notice *****
>>
>> This message is private and confidential. If you have received this
>> message in error, please notify us and remove it from your system.
>>
>>
>> Simwood eSMS Limited is a limited company registered in England and Wales.
>> Registered number: 03379831. Registered office: c/o HW Chartered
>> Accountants, Keepers Lane, The Wergs, Wolverhampton, WV6 8UA. Trading
>> address: Falcon Drive, Cardiff Bay, Cardiff, CF10 4RU.
>>
>>
>>
>
Re: Conditional joins
Posted by Kenneth Tyler <ke...@8thfold.com>.
Simon,
I think this is a case where the flat document structure of couch documents
makes things much simpler than in SQL.
If you had a view like this:
function(doc){if(doc.type=='user'){emit(doc.status,doc)}
then it would return all the user documents with their status as their key
and you could use ?key="1" (i think the quotes are necessary) to get all the
users whose status is 1
if you want to do the same thing for domains, you just write a "domain" view
that returns status as the key
ken tyler
On Fri, Sep 17, 2010 at 6:04 AM, Simon Woodhead
<si...@simwood.com>wrote:
> Hi folks,
>
> I'm pretty green with views and have a need to do a join with results
> from one side returned if a condition is met on the other. In SQL I'd
> do:
>
> select * from child join parent on child.id=parent.id where
> parent.status=1;
>
> I've read the 'join' documentation (e.g.
> http://www.cmlenz.net/archives/2007/10/couchdb-joins) and can return
> documents of multiple types with matching keys ok. What I can't do is
> do the same but only if the parent matches a condition.
>
> Specifically, my data is like:
>
>
> {"45a6951b34a6f45bd44fd169c0003f9c","_rev":"1-6c88818b43d562c45e452bf00db06ee2","type":"domain","domain":"
> test.com","status":1},
>
> {"_id":"45a6951b34a6f45bd44fd169c00046db","_rev":"1-37eaf0c4944dac9f5fe07b25c0b68b1d","type":"domain","domain":"
> test2.com","status":0},
>
> {"_id":"45a6951b34a6f45bd44fd169c00053d9","_rev":"3-729f46c86ee4984941939c65787126fb","type":"user","domain":"
> test.com","user":"foo","password":"test123"},
>
> {"_id":"45a6951b34a6f45bd44fd169c00060c8","_rev":"1-46997eba12e7adbb6e9266a1fe83c42d","type":"user","domain":"
> test2.com","user":"bar","password":"test123"}
>
> I want to be be able to query a view by domain and see all users for
> it if the status is 1. So in this case I'd see users for test.com but
> not for test2.com.
> Any help would be appreciated.
>
> Thanks!
> Simon
> --
> ***** Email confidentiality notice *****
>
> This message is private and confidential. If you have received this message
> in error, please notify us and remove it from your system.
>
>
> Simwood eSMS Limited is a limited company registered in England and Wales.
> Registered number: 03379831. Registered office: c/o HW Chartered
> Accountants, Keepers Lane, The Wergs, Wolverhampton, WV6 8UA. Trading
> address: Falcon Drive, Cardiff Bay, Cardiff, CF10 4RU.
>
>
>