From user-return-12734-apmail-couchdb-user-archive=couchdb.apache.org@couchdb.apache.org Fri Sep 17 13:57:44 2010 Return-Path: Delivered-To: apmail-couchdb-user-archive@www.apache.org Received: (qmail 49860 invoked from network); 17 Sep 2010 13:57:44 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 17 Sep 2010 13:57:44 -0000 Received: (qmail 30345 invoked by uid 500); 17 Sep 2010 13:57:42 -0000 Delivered-To: apmail-couchdb-user-archive@couchdb.apache.org Received: (qmail 30278 invoked by uid 500); 17 Sep 2010 13:57:39 -0000 Mailing-List: contact user-help@couchdb.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@couchdb.apache.org Delivered-To: mailing list user@couchdb.apache.org Received: (qmail 30263 invoked by uid 99); 17 Sep 2010 13:57:39 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 17 Sep 2010 13:57:39 +0000 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests=FREEMAIL_FROM,RCVD_IN_DNSWL_NONE,SPF_PASS,T_TO_NO_BRKTS_FREEMAIL X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of mrtrick@gmail.com designates 209.85.210.52 as permitted sender) Received: from [209.85.210.52] (HELO mail-pz0-f52.google.com) (209.85.210.52) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 17 Sep 2010 13:57:34 +0000 Received: by pzk27 with SMTP id 27so2698809pzk.11 for ; Fri, 17 Sep 2010 06:57:14 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from :user-agent:mime-version:to:subject:references:in-reply-to :content-type:content-transfer-encoding; bh=ZMGfa0m90Ep4vG6u/TlUqV33Y8qR59z9gay67F7h5xY=; b=mdsemtC36AeoM7I0i1YxRwgA0NROcDddBVQduFkoiDiZHmQvLDRVhKLZ9r6sOksvkC qg1r2Bsm8m4LkB+8vgZfIO7YaJ9DYGBaQJPnY4Rjz4xwoAGY9scA4DRhI2WN8uQjXGNe 00CYqGFmpt2YV67if2J7bpjbSv/auee02po34= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:subject:references :in-reply-to:content-type:content-transfer-encoding; b=MUWsJeUKHT4noODrZ/6sB4TMJdsw39WeWKLNia9/TSBI+rz5TBUrPXuHfku7pbN5Ea CKPwNGQNEVUJ8ZM9Ilou0QbrDFYdxRZRNVCeoCS1xANwus1LaGBEIu5/dRtyas5/TLYZ O5mP2dVeDqPbT4lC58RagropMARMH/CWxiFV8= Received: by 10.114.195.12 with SMTP id s12mr5491168waf.14.1284731833947; Fri, 17 Sep 2010 06:57:13 -0700 (PDT) Received: from [192.168.1.99] (124-168-131-189.dyn.iinet.net.au [124.168.131.189]) by mx.google.com with ESMTPS id c24sm6596936wam.19.2010.09.17.06.57.11 (version=TLSv1/SSLv3 cipher=RC4-MD5); Fri, 17 Sep 2010 06:57:12 -0700 (PDT) Message-ID: <4C9373AE.9050508@gmail.com> Date: Fri, 17 Sep 2010 23:57:02 +1000 From: Patrick Barnes User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.9) Gecko/20100825 Lightning/1.0b2 Thunderbird/3.1.3 MIME-Version: 1.0 To: user@couchdb.apache.org Subject: Re: Conditional joins References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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. > > >