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 [email protected]. For more options, visit https://groups.google.com/d/optout.
