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:53:33 GMT
"Its all about sorting."

totally agreed. Understanding that has helped me work it out, at the start 
I was looking at endkey like it was the last letter in the key (eg 
startkey-C endkey-r = Computer)..yes I am a noob.

However, did you have a look at the example I sent from a tutorial using 
regex's? wouldn't that be overkill / not even work at all?



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



i < k < l < m

You have keys starting with k and l. AFAICT, it worked and you just
don't have data to indicate the difference.

Try:

endkey="l"

while switching between inclusive_end=true and inclusive_end=false

Its all about sorting.

On Wed, Jun 2, 2010 at 12:39 AM,  <Steven.Prentice@nextgen.net> wrote:
> 
_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