couchdb-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Paul Davis <paul.joseph.da...@gmail.com>
Subject Re: Simulating SQL 'LIKE %' using Regular Expressions
Date Wed, 02 Jun 2010 05:01:57 GMT
Must've missed that the first time. Near as I can tell that example is
doing something like:

VALUE -> KEY
"image/gif"  -> "image/"
"image/png" -> "image/"
"text/plain" -> "text/"
"text/xml" -> "text/"

And then letting users select by "text/" or "image/"

as opposed to a free-form "tex" or "im"

which is more like you were wanting.

On Wed, Jun 2, 2010 at 12:53 AM,  <Steven.Prentice@nextgen.net> wrote:
> "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
View raw message