you can use "contains" for 'text' fields if you want it turned to a "LIKE '%whatever%' query, or as a "find me where 'whatever' is" in a 'list:string' field. at this point a bit of code wouldn't hurt :D On Thursday, April 17, 2014 5:07:38 PM UTC+2, Rob Goldsmith wrote: > > Thanks Joe - changing the query to *belongs *did the trick although the > query is still very slow when the list is long - but I'm not sure if that's > primarily because I'm using sqlite at this stage? > > Niphlod - f_postcode is of type text and result is a list of strings (e.g. > ['W1 2TR', 'SW1 5AB' ...] > > On Wednesday, 16 April 2014 13:39:02 UTC+1, Rob Goldsmith wrote: >> >> I tried posting this earlier, but it doesn't seem to have appeared yet - >> so apologies if this comes up twice. >> >> I am writing a web2py application which accepts a UK postcode then >> provides information about nearby postcodes. I have tried two methods, both >> of which work fine when the number of nearby postcodes is relatively few >> (less than a hundred). >> >> Method 1 uses the *contains *method as follows >> >> query &= db.t_properties.f_postcode.contains(result) >> >> where *result *is the list of nearby postcodes >> >> Method 2 does the query first and then uses *exclude* to eliminate the >> unwanted rows as follows >> >> rows.exclude(lambda row: row.f_postcode not in result) >> >> However, when the list of postcodes is larger (>about 100), both methods >> are failing. Method 1 appears to fail because of inherent limitations of >> sqlite (https://sqlite.org/limits.html - SQLITE_LIMIT_VARIABLE_NUMBER - >> albeit that refers to a limit of 999 rather than 99). Method 2 fails with >> the message: >> >> <type 'exceptions.RuntimeError'> maximum recursion depth exceeded in cmp >> >> Now, I'm pretty sure that I can solve it by moving to MySQL or another db >> but I'm pretty sure that I'm not doing the query in the optimal way. Can >> anyone give any advice as to the correct / most efficient way to accomplish >> this? >> >> thanks >> Rob. >> >>
-- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
