I hadn't thought about that. My first thought is that I'd like to keep as much as possible within web2py for portability, but I will have to look at that possibility. Thanks. On Tuesday, November 27, 2012 2:21:09 PM UTC-5, Derek wrote: > > 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? >>>>>> >>>>>> >>>>>>
--
