Mailing-List: contact user-help@couchdb.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@couchdb.apache.org
Received-SPF: pass (nike.apache.org: domain of paul.joseph.davis@gmail.com
 designates 74.125.46.30 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=mime-version:in-reply-to:references:date:message-id:subject:from:to
         :content-type:content-transfer-encoding;
        b=ej0NxltF5nZf7VCZ7F7LJDB0DRStZSIZyYjmakQ1aCyAFD0d6waNpyIb5cxHn55a0G
         zn61WmuRasrmNytjyf+LtsjR08I/yQkd42EIdj1fSj84MX6u1GRO0ANqs/87Yej/sHvD
         OJ8K+Yz4xsRGZ6SNgvwApjyBZeY6cLu1kgXXU=
MIME-Version: 1.0
In-Reply-To: <e8d26ac40904251027v70771c75ucd3e29d5b06fb6d@mail.gmail.com>
References: <e8d26ac40904251027v70771c75ucd3e29d5b06fb6d@mail.gmail.com>
Date: Sat, 25 Apr 2009 14:01:58 -0400
Message-ID: <e2111bbb0904251101u1a8bec44ie4449f3c5095490a@mail.gmail.com>
Subject: Re: Querying by foreign keys
From: Paul Davis <paul.joseph.davis@gmail.com>
To: user@couchdb.apache.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Adam,

Yeah, this is one of the harder bits to do in CouchDB at the moment.
Your two best bets are either to use something like couchdb-lucene or
as you mentioned to pay attention to the key frequencies for smarter
retrieval and then do client side filtering.

HTH,
Paul Davis

On Sat, Apr 25, 2009 at 1:27 PM, Adam Wolff <awolff@gmail.com> wrote:
> Hi list,
> I have a little query problem that would be easy to solve in SQL, but
> seems hard in CouchDB. I have data that looks like this:
> doc1: {
> =A0 keys : [1,2,3],
> =A0 data : ....
> }
> doc2: {
> =A0 keys : [1,3,5],
> =A0 data : ....
> }
> ...
>
> I'd like a view of my documents that lets me filter on multiple keys,
> so a query of keys=3D[1] yields doc1,doc2, as does a query of
> keys=3D[1,3]. keys=3D[2] would just fetch doc1, etc. Documents commonly
> have 1-5 keys, but may have thousands.
>
> In SQL, this would be easy to model by making a table of doc.id,key.
> In couch, the only way I can think of to truly index all the
> combinations of the keys is to emit the n! combinations of the keys,
> which seems like a bad idea. (Maybe it's not though?)
>
> So far, the best solution we've thought of is to emit the document for
> each key. We would use the information about key frequency to be smart
> about fetching an initial list of documents, and then do the rest of
> the filtering in the app.
>
> Any advice here? I looked at
> http://wiki.apache.org/couchdb/EntityRelationship, but there's no
> entry on querying by foreign keys. If we come up with something good
> (or even just definitive), of course I'll add it to the wiki.
>
> Thanks!
> A
>