For this kind of thing, triggers are great. On Tuesday, November 27, 2012 10:23:13 AM UTC-7, Wes Hall wrote: > > Understood, this is a database issue. My expense comment was referring to > frequently checking the db for changes, not the actual query. > > Friendly names should < 10, teacher list should remain < 1000. > > It does sound as though my best bet is to monitor the "friendly name" > manage page, and on insert/update, re-flag the teacher table. I would also > need to get the teachers flagged on insert. Then, the final teacher query, > as you mentioned, is trivial. > > Thanks again for your commitment to Web2Py. > > > On Tuesday, November 27, 2012 11:32:59 AM UTC-5, Massimo Di Pierro wrote: >> >> This is not really a web2py issue. This is a database issue. Who would >> you do this efficiently with SQL? Unless you store the flag, you can try >> using regex search. It will only work with some databases and it may not be >> more efficient. How many friendly names do you have? >> >> On Tuesday, 27 November 2012 10:25:32 UTC-6, Wes Hall wrote: >>> >>> Since I obfuscated the real application, I can't fault this answer. >>> However, in the current usage, it doesn't feel like the best solution. >>> Teachers are being inserted into the database from outside Web2Py, while >>> the friendly names are managed from within (and might change frequently, >>> which would require adjusting existing rows). A cron task could update the >>> database using the suggested logic, but that seems a little expensive for >>> what needs to happen. >>> >>> At any rate, it appears as though I'm not missing some Web2Py magic that >>> would simplify the query. >>> >>> On Tuesday, November 27, 2012 10:53:39 AM UTC-5, Massimo Di Pierro wrote: >>>> >>>> I would suggest that when a user register you check if they have >>>> "friendly sounding" first name and flag it, then search using the flag. >>>> >>>> On Tuesday, 27 November 2012 09:48:48 UTC-6, Wes Hall wrote: >>>>> >>>>> I want to select teachers that have taught for more than one year, are >>>>> getting paid and have a friendly sounding first name. >>>>> The friendly sounding names are stored in a table, along with their >>>>> expected position in the name. >>>>> >>>>> Currently I'm using this: >>>>> >>>>> teacher_query = db.teachers.years_taught!=0 >>>>> teacher_query &= db.teachers.status=='Paid' >>>>> >>>>> whitelisting = db.teachers.first_name=='zzzzzzzzzzz' >>>>> >>>>> friendly = db(db.friendly_names).select(db.friendly_names.name >>>>> ,db.friendly_names.position) >>>>> for x in friendly: >>>>> if x['position'] == 'Leading': >>>>> whitelisting |= db.teachers.first_name.startswith(x['name']) >>>>> else: >>>>> whitelisting |= db.teachers.first_name.contains(x['name']) >>>>> >>>>> teacher_query &= whitelisting >>>>> >>>>> Is there a better way to get the whitelist sub-query started? >>>>> Should I do a counter inside the for loop and initialize >>>>> 'whitelisting' if it's the first friendly name, or is there a method I'm >>>>> missing from W2P? >>>>> >>>>> >>>>>
--
