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