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? >>>> >>>> >>>> --
