You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@couchdb.apache.org by Duy Nguyen <du...@yahoo.com> on 2009/10/30 18:36:09 UTC
CouchDB equivalent dynamic SQL
Hi guys,
I have a troublesome sql query that needs to translate to couchDB map/reduce
SELECT * FROM PRODUCTSWHERE [ PRICE ] AND [TYPE]'
Please note that Price and Type are dynamically generated depend on user inputs. ( i.e TYPEcan be 100 < PRICE < 1000, or 1 < PRICE < 100, or 1000 < PRICE < 10000. TYPE could be 'computer' , 'tv' etc..)
I can not do dynamically query on the fly with CouchDB. what is the best way to solve this problem ?
Re: CouchDB equivalent dynamic SQL
Posted by Adam Wolff <aw...@gmail.com>.
If there's a limited number of TYPEs, then emit a view like this:
emit([doc.type, doc.price], ...
and query like /.../_view/...?startkey=["tv", 100]&endkey=["tv",1000]
A
On Fri, Oct 30, 2009 at 10:36 AM, Duy Nguyen <du...@yahoo.com>wrote:
> Hi guys,
> I have a troublesome sql query that needs to translate to couchDB
> map/reduce
> SELECT * FROM PRODUCTSWHERE [ PRICE ] AND [TYPE]'
> Please note that Price and Type are dynamically generated depend on user
> inputs. ( i.e TYPEcan be 100 < PRICE < 1000, or 1 < PRICE < 100, or 1000
> < PRICE < 10000. TYPE could be 'computer' , 'tv' etc..)
> I can not do dynamically query on the fly with CouchDB. what is the best
> way to solve this problem ?
>
>
>
Re: CouchDB equivalent dynamic SQL
Posted by Adam Wolff <aw...@gmail.com>.
You can make two queries
?startkey= [ "computer", 1000]&endkey=["computer" , 2000]
?startkey= ["tv", 1000]&endkey=["tv" , 2000]
If couch had multi-range queries(
https://issues.apache.org/jira/browse/COUCHDB-523), you could do this in one
request.
alternatively, depending on the shape of your data, you could just index
everything by price and filter types in your application. my understanding
is that this is what a SQL db would do on the server-side.
A
On Fri, Oct 30, 2009 at 1:46 PM, Duy Nguyen <du...@yahoo.com> wrote:
> Thanks for reply , guys. If i want to select all tv or computer that have
> price in between 1000 and 2000,
> ?startkey= [ ["computer", "tv"] , 1000]&endkey=[["computer", "tv"] ,
> 2000]&include_docs=true
> Is it correct ?
>
> --- On Sat, 10/31/09, Adam Kocoloski <ko...@apache.org> wrote:
>
> From: Adam Kocoloski <ko...@apache.org>
> Subject: Re: CouchDB equivalent dynamic SQL
> To: user@couchdb.apache.org
> Date: Saturday, October 31, 2009, 12:59 AM
>
> On Oct 30, 2009, at 1:36 PM, Duy Nguyen wrote:
>
> > Hi guys,
> > I have a troublesome sql query that needs to translate to couchDB
> map/reduce
> > SELECT * FROM PRODUCTSWHERE [ PRICE ] AND [TYPE]'
> > Please note that Price and Type are dynamically generated depend on user
> inputs. ( i.e TYPEcan be 100 < PRICE < 1000, or 1 < PRICE < 100, or 1000 <
> PRICE < 10000. TYPE could be 'computer' , 'tv' etc..)
> > I can not do dynamically query on the fly with CouchDB. what is the best
> way to solve this problem ?
>
> Hi Duy, it won't always be a perfect translation. But CouchDB's compound
> view keys should be able to help you in this situation. In your map,
> emit([doc.type, doc.price], null) and then query the view with
>
> ?startkey=["computer", 1000]&endkey=["computer", 10000]&include_docs=true
>
> or
>
> ?startkey=["tv", 100]&endkey=["tv", 1000]&include_docs=true
>
> Best, Adam
>
>
>
>
>
>
Re: CouchDB equivalent dynamic SQL
Posted by Zachary Zolton <za...@gmail.com>.
> fwiw couchdb-lucene 0.5 will have better numeric support.
That's good news, indeed!
Re: CouchDB equivalent dynamic SQL
Posted by Robert Newson <ro...@gmail.com>.
fwiw couchdb-lucene 0.5 will have better numeric support.
B.
On Fri, Oct 30, 2009 at 1:59 PM, Adam Kocoloski <ko...@apache.org> wrote:
> On Oct 30, 2009, at 1:36 PM, Duy Nguyen wrote:
>
>> Hi guys,
>> I have a troublesome sql query that needs to translate to couchDB
>> map/reduce
>> SELECT * FROM PRODUCTSWHERE [ PRICE ] AND [TYPE]'
>> Please note that Price and Type are dynamically generated depend on user
>> inputs. ( i.e TYPEcan be 100 < PRICE < 1000, or 1 < PRICE < 100, or 1000 <
>> PRICE < 10000. TYPE could be 'computer' , 'tv' etc..)
>> I can not do dynamically query on the fly with CouchDB. what is the best
>> way to solve this problem ?
>
> Hi Duy, it won't always be a perfect translation. But CouchDB's compound
> view keys should be able to help you in this situation. In your map,
> emit([doc.type, doc.price], null) and then query the view with
>
> ?startkey=["computer", 1000]&endkey=["computer", 10000]&include_docs=true
>
> or
>
> ?startkey=["tv", 100]&endkey=["tv", 1000]&include_docs=true
>
> Best, Adam
>
>
Re: CouchDB equivalent dynamic SQL
Posted by Alex P <ko...@gmail.com>.
That will only work if you emit all the possible combinations of two
items. Basically range query parameters have to have be the same form
as the keys that you emit. If you want to do set operations on top of
that, you either need lucene or do it manually on the client.
Sent from my iPhone
On Oct 30, 2009, at 3:46 PM, Duy Nguyen <du...@yahoo.com> wrote:
> Thanks for reply , guys. If i want to select all tv or computer that
> have price in between 1000 and 2000,
> ?startkey= [ ["computer", "tv"] , 1000]&endkey=[["computer", "tv"] ,
> 2000]&include_docs=true
> Is it correct ?
>
> --- On Sat, 10/31/09, Adam Kocoloski <ko...@apache.org> wrote:
>
> From: Adam Kocoloski <ko...@apache.org>
> Subject: Re: CouchDB equivalent dynamic SQL
> To: user@couchdb.apache.org
> Date: Saturday, October 31, 2009, 12:59 AM
>
> On Oct 30, 2009, at 1:36 PM, Duy Nguyen wrote:
>
>> Hi guys,
>> I have a troublesome sql query that needs to translate to couchDB
>> map/reduce
>> SELECT * FROM PRODUCTSWHERE [ PRICE ] AND [TYPE]'
>> Please note that Price and Type are dynamically generated depend on
>> user inputs. ( i.e TYPEcan be 100 < PRICE < 1000, or 1 < PRICE <
>> 100, or 1000 < PRICE < 10000. TYPE could be 'computer' , 'tv' etc..)
>> I can not do dynamically query on the fly with CouchDB. what is the
>> best way to solve this problem ?
>
> Hi Duy, it won't always be a perfect translation. But CouchDB's
> compound view keys should be able to help you in this situation. In
> your map, emit([doc.type, doc.price], null) and then query the view
> with
>
> ?startkey=["computer", 1000]&endkey=["computer", 10000]
> &include_docs=true
>
> or
>
> ?startkey=["tv", 100]&endkey=["tv", 1000]&include_docs=true
>
> Best, Adam
>
>
>
>
Re: CouchDB equivalent dynamic SQL
Posted by Duy Nguyen <du...@yahoo.com>.
Thanks for reply , guys. If i want to select all tv or computer that have price in between 1000 and 2000,
?startkey= [ ["computer", "tv"] , 1000]&endkey=[["computer", "tv"] , 2000]&include_docs=true
Is it correct ?
--- On Sat, 10/31/09, Adam Kocoloski <ko...@apache.org> wrote:
From: Adam Kocoloski <ko...@apache.org>
Subject: Re: CouchDB equivalent dynamic SQL
To: user@couchdb.apache.org
Date: Saturday, October 31, 2009, 12:59 AM
On Oct 30, 2009, at 1:36 PM, Duy Nguyen wrote:
> Hi guys,
> I have a troublesome sql query that needs to translate to couchDB map/reduce
> SELECT * FROM PRODUCTSWHERE [ PRICE ] AND [TYPE]'
> Please note that Price and Type are dynamically generated depend on user inputs. ( i.e TYPEcan be 100 < PRICE < 1000, or 1 < PRICE < 100, or 1000 < PRICE < 10000. TYPE could be 'computer' , 'tv' etc..)
> I can not do dynamically query on the fly with CouchDB. what is the best way to solve this problem ?
Hi Duy, it won't always be a perfect translation. But CouchDB's compound view keys should be able to help you in this situation. In your map, emit([doc.type, doc.price], null) and then query the view with
?startkey=["computer", 1000]&endkey=["computer", 10000]&include_docs=true
or
?startkey=["tv", 100]&endkey=["tv", 1000]&include_docs=true
Best, Adam
Re: CouchDB equivalent dynamic SQL
Posted by Adam Kocoloski <ko...@apache.org>.
On Oct 30, 2009, at 1:36 PM, Duy Nguyen wrote:
> Hi guys,
> I have a troublesome sql query that needs to translate to couchDB
> map/reduce
> SELECT * FROM PRODUCTSWHERE [ PRICE ] AND [TYPE]'
> Please note that Price and Type are dynamically generated depend on
> user inputs. ( i.e TYPEcan be 100 < PRICE < 1000, or 1 < PRICE <
> 100, or 1000 < PRICE < 10000. TYPE could be 'computer' , 'tv' etc..)
> I can not do dynamically query on the fly with CouchDB. what is the
> best way to solve this problem ?
Hi Duy, it won't always be a perfect translation. But CouchDB's
compound view keys should be able to help you in this situation. In
your map, emit([doc.type, doc.price], null) and then query the view with
?startkey=["computer", 1000]&endkey=["computer", 10000]
&include_docs=true
or
?startkey=["tv", 100]&endkey=["tv", 1000]&include_docs=true
Best, Adam
Re: CouchDB equivalent dynamic SQL
Posted by Zachary Zolton <za...@gmail.com>.
CouchDB-Lucene could be used here, but you have to taken into account
that Lucene will treat the numeric values as strings.
Cheers,
Zach
On Friday, October 30, 2009, Duy Nguyen <du...@yahoo.com> wrote:
> Hi guys,
> I have a troublesome sql query that needs to translate to couchDB map/reduce
> SELECT * FROM PRODUCTSWHERE [ PRICE ] AND [TYPE]'
> Please note that Price and Type are dynamically generated depend on user inputs. ( i.e TYPEcan be 100 < PRICE < 1000, or 1 < PRICE < 100, or 1000 < PRICE < 10000. TYPE could be 'computer' , 'tv' etc..)
> I can not do dynamically query on the fly with CouchDB. what is the best way to solve this problem ?
>
>
>