couchdb-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Steven.Prent...@nextgen.net
Subject Re: Simulating SQL 'LIKE %' using Regular Expressions
Date Wed, 02 Jun 2010 04:39:11 GMT
_design/inventory/_view/store_count_by_product?startkey=%22i%22&endkey=%22m%22&inclusive_end=false


{"total_rows":30,"offset":6,"rows":[
{"id":"p4","key":"k comp","value":{"Store":"CompuShop","qty":23}},
{"id":"p4","key":"k comp","value":{"Store":"Kerry's Computer 
Shop","qty":56}},
{"id":"p4","key":"k comp","value":{"Store":"Store A","qty":45}},
{"id":"p1","key":"keyboard","value":{"Store":"CompuShop","qty":300000}},
{"id":"p1","key":"keyboard","value":{"Store":"Kerry's Computer 
Shop","qty":100000}},
{"id":"p1","key":"keyboard","value":{"Store":"Store A","qty":200000}},
{"id":"p6","key":"lenovo think smart desktop 
PC","value":{"Store":"CompuShop","qty":6}},
{"id":"p6","key":"lenovo think smart desktop PC","value":{"Store":"Kerry's 
Computer Shop","qty":4}},
{"id":"p6","key":"lenovo think smart desktop PC","value":{"Store":"Store 
A","qty":2}}
]}


(all keys starting with K and L)

Whereas omitting &inclusive_end=false did exactly the same thing





From:
Paul Davis <paul.joseph.davis@gmail.com>
To:
user@couchdb.apache.org
Date:
02/06/2010 02:27 PM
Subject:
Re: Simulating SQL 'LIKE %' using Regular Expressions



I just got reminded by vspy on IRC that there's an inclusive_end 
parameter.

What does a URL like this give you:

?startkey="C"&endkey="D"&inclusive_end=false

Though those mechanics are kinda rough. What about:

?startkey="C"&endkey="C\u0000"&inclusive_end=false

Although, I swear I remember a better way to do this...

On Tue, Jun 1, 2010 at 9:56 PM,  <Steven.Prentice@nextgen.net> wrote:
> Understood, BUT what if I had an extra product, your thinking "obviously
> just set the limit to 11" or something like that.
> I am wanting it to be a bit more dynamic so that I do not have to know 
how
> many to limit the results to until it shows something that is not like 
C%
>
> For example with what you have given me to try, limit=10 works fine, if 
i
> change it to 11, the 11th value of the key is "Store A"..clearly not 
LIKE
> C%
>
> I tried endkey and abused unicode, but that resulted in a JSON error.
>
> What I am trying to say (I think) is when I run the view, I want the 
rows
> returned to ONLY be those LIKE C% so that I do not require a limit at 
all,
> it just shows all of them based on the query (i.e C, Co, Comp etc etc)
>
> is this just something that is impossible with couchDB? I'm sorry if I
> sound really dumb and noobish, I am very very very new to couchDB and 
the
> concept of documents as opposed to tables
>
> would something like this ever work? (extract from a tutorial)
>
> The clue lies in extracting the prefix we want to search for from our
> document and put it into our view index. We use a regular expression to
> match our prefix:
> function(doc) {
>  if(doc["mime-type"]) {
>    // from the start (^) match everything that is not a slash ([^\/]+)
> until
>    // we find a slash (\/). Slashes needs to be escaped with a backslash
> (\/)
>    var prefix = doc["mime-type"].match(/^[^\/]+\//);
>    if(prefix) {
>      emit(prefix, null);
>    }
>  }
> }
> Comment on topic or styleYou can now query this view with your desired
> mime-type prefix and not only find all images, but also text, and video
> and all other formats:
> /files/_design/finder/_view/by-mime-type?key="image/"
>
>
>
> From:
> Paul Davis <paul.joseph.davis@gmail.com>
> To:
> user@couchdb.apache.org
> Date:
> 02/06/2010 11:33 AM
> Subject:
> Re: Simulating SQL 'LIKE %' using Regular Expressions
>
>
>
> The short answer is that you can do 'like "C%"' but not 'like "%C"'.
> You don't really appear to care about the latter so this should be ok.
>
> To accomplish:
>
> //map function
> function(doc) {
>  for(id in doc.Stores) {
>    if(doc.Stores[id].name) {
>      emit(doc.Stores[id].name, {
>        "product": doc.product_name,
>        "qty": doc.Stores[id].item_count
>      });
>    }
> }
>
> And then to query you use something like:
>
> http://hostname/db_name/_design/ddocid/_view/products_by_store?startkey=
> "C"&limit=10
>
> to get the first 10 matching stores.
>
> To get the first N stores matching a prefix of the name for a given
> product, do something like:
>
> //in map function
> emit([doc.product_name, doc.Stores[id].name], product_info_obj);
>
> and query the view with the query string:
>
> ?startkey=[product_name, "C"]&limit=10
>
> Remember that views are all about sorting. Your prefix dealie is just
> asking for "the next N things that sort after this prefix" which is
> totally doable.
>
> The inevitable follow up will be "but it shows stores that start with
> Dude" when I query for "C" if there aren't any stores starting with
> "C".
>
> In this case you just use an endkey that limits the results to the
> range you're interested in. Kinda sorta abusing unicode bits, you can
> do something like:
>
> ?startkey="C"&endkey="C\uFFFF"&limit=10 which will limit things
> reasonably. I seem to recall a different string sentinel but the
> alternate version escapes me at the moment.
>
> Also, this should work with things like:
>
> ?startkey="Computer"&endkey="Computer\uFFFF"&limit=10
>
> to get the list of stores starting with "Computer"
>
>
> On Tue, Jun 1, 2010 at 9:01 PM,  <Steven.Prentice@nextgen.net> wrote:
>> Hi, so in my couchDB, what I want to happen is when you query a view in
>> the design doc and providing ?key="C" at the end, bring up all shop
> names
>> that start with C.
>> to do this I have made the following map function:
>>
>> function(doc)
>> {
>>
>>  for (id in doc.Stores)
>>  {
>>
>>  var strqry = doc.Stores[id]["name"].match(/^.*/);
>>
>>    if (strqry)
>>    {
>>      emit(strqry, doc.product_name + " qty: " +
>> doc.Stores[id].item_count);
>>    }
>>  }
>> }
>>
>> When I run this from the temp view in Futon, I get a list of all stores
>> and products (which made me think 'yay it worked, all it needs is a
>> parameter') but when I use:
>> http://host:5984/db/design/name/_view/function?key="C" I get back:
>> {"total_rows":30,"offset":0,"rows":[]}
>>
>> my ultimate aim is to get it working similar to an SQL Like % so if for
>> example I say ?key="C" it will return "Computer Store A" and so on..
>> I created my function based on this tutorial:
>> http://books.couchdb.org/relax/reference/views-for-sql-jockeys
>>
>>
>>
>
>
>



Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message