[web2py] Re: Multiple like clauses
You are probably correct! The problem here is legacy though. Changing that
column now, means a lot of work. Thank you both for your replies!
Den tisdagen den 27:e november 2012 kl. 20:40:41 UTC+1 skrev Joe Barnhart:
>
> I think niphlod is correct. The query you have chosen is rather difficult
> in SQL. The "contains" function maps onto the SQL "like" syntax, and that
> has no ability to handle a list of targets, only a single string with
> wildcards. To achieve the effect you want, you essentially have to create
> another "contains" statement for every item in the list.
>
> You might give some thought to the underlying problem and see if there is
> a different way to express the data relationships to accomplish your
> goals. Perhaps the "prefix" part should be in its own column -- then you
> could use the SQL "in" syntax (web2py calls it "belongs") which does take a
> list or even a subquery as its argument.
>
> -- Joe B.
>
> On Monday, November 26, 2012 3:58:29 AM UTC-8, Joel Samuelsson wrote:
>>
>> I have a function which takes a list of prefixes the data from a select
>> can start with. The list is of unknown size. I found that the contains
>> function (db.mytable.myfield.contains('value')) can take a list instead
>> of 'value'. When I try this with the startswith function (
>> db.mytable.myfield.contains('value')) I get an error. Is there any way I
>> can get similar behaviour to contains but with startswith without moding
>> web2py?
>>
>> Best regards,
>> Joel
>>
>
--
[web2py] Re: Multiple like clauses
I think niphlod is correct. The query you have chosen is rather difficult
in SQL. The "contains" function maps onto the SQL "like" syntax, and that
has no ability to handle a list of targets, only a single string with
wildcards. To achieve the effect you want, you essentially have to create
another "contains" statement for every item in the list.
You might give some thought to the underlying problem and see if there is a
different way to express the data relationships to accomplish your goals.
Perhaps the "prefix" part should be in its own column -- then you could use
the SQL "in" syntax (web2py calls it "belongs") which does take a list or
even a subquery as its argument.
-- Joe B.
On Monday, November 26, 2012 3:58:29 AM UTC-8, Joel Samuelsson wrote:
>
> I have a function which takes a list of prefixes the data from a select
> can start with. The list is of unknown size. I found that the contains
> function (db.mytable.myfield.contains('value')) can take a list instead
> of 'value'. When I try this with the startswith function (
> db.mytable.myfield.contains('value')) I get an error. Is there any way I
> can get similar behaviour to contains but with startswith without moding
> web2py?
>
> Best regards,
> Joel
>
--
[web2py] Re: Multiple like clauses
you need to build the query dinamically
queries = []
for segment in mylist:
queries.append(db.mytable.myfield.startswith(segment))
query = reduce(lambda a,b:(a|b),queries)
results = db(query).select()
Il giorno lunedì 26 novembre 2012 12:58:29 UTC+1, Joel Samuelsson ha
scritto:
>
> I have a function which takes a list of prefixes the data from a select
> can start with. The list is of unknown size. I found that the contains
> function (db.mytable.myfield.contains('value')) can take a list instead
> of 'value'. When I try this with the startswith function (
> db.mytable.myfield.contains('value')) I get an error. Is there any way I
> can get similar behaviour to contains but with startswith without moding
> web2py?
>
> Best regards,
> Joel
>
--
