If you benefit from web2py hope you feel encouraged to pay it forward by contributing back to society in whatever form you choose!

Setting up

Let's create a model with interdependent values and populate it with sample data.

db.define_table('state',
                Field('name'),format='%(name)s')
db.define_table('city',
                Field('name'),
                Field('state',db.state),format='%(name)s')
db.define_table('zipcode',
                Field('value'),
                Field('city',db.city),format='%(value)s')
db.define_table('areacode',
                Field('value'),
                Field('zipcode',db.zipcode),format='%(value)s')

db.define_table('phone',
                Field('areacode',db.areacode),
                Field('number'))

if db(db.state.id>0).count() == 0:
    db.state.truncate()
    db.city.truncate()
    db.zipcode.truncate()
    db.areacode.truncate()
    db.state.insert(name='Texas')
    db.state.insert(name='Illinois')
    db.state.insert(name='California')

    db.city.insert(name='Austin',state=1)
    db.city.insert(name='Dallas',state=1)
    db.city.insert(name='Chicago',state=2)
    db.city.insert(name='Aurora',state=2)
    db.city.insert(name='Los Angeles',state=3)
    db.city.insert(name='San Diego',state=3)

    db.zipcode.insert(value='78704',city=1)
    db.zipcode.insert(value='78745',city=1)
    db.zipcode.insert(value='75001',city=2)
    db.zipcode.insert(value='75038',city=2)
    db.zipcode.insert(value='60606',city=3)
    db.zipcode.insert(value='60607',city=3)
    db.zipcode.insert(value='60504',city=4)
    db.zipcode.insert(value='60505',city=4)
    db.zipcode.insert(value='90005',city=5)
    db.zipcode.insert(value='90006',city=5)
    db.zipcode.insert(value='92101',city=6)
    db.zipcode.insert(value='92102',city=6)

    db.areacode.insert(value='512',zipcode=1)
    db.areacode.insert(value='511',zipcode=1)
    db.areacode.insert(value='345',zipcode=2)
    db.areacode.insert(value='456',zipcode=2)
    db.areacode.insert(value='567',zipcode=3)
    db.areacode.insert(value='678',zipcode=3)
    db.areacode.insert(value='789',zipcode=4)
    db.areacode.insert(value='890',zipcode=4)
    db.areacode.insert(value='901',zipcode=5)
    db.areacode.insert(value='321',zipcode=5)
    db.areacode.insert(value='432',zipcode=6)
    db.areacode.insert(value='534',zipcode=6)
    db.areacode.insert(value='645',zipcode=7)
    db.areacode.insert(value='765',zipcode=7)
    db.areacode.insert(value='876',zipcode=8)
    db.areacode.insert(value='987',zipcode=8)
    db.areacode.insert(value='141',zipcode=9)
    db.areacode.insert(value='252',zipcode=9)
    db.areacode.insert(value='363',zipcode=10)
    db.areacode.insert(value='474',zipcode=10)
    db.areacode.insert(value='585',zipcode=11)
    db.areacode.insert(value='686',zipcode=11)
    db.areacode.insert(value='797',zipcode=12)
    db.areacode.insert(value='898',zipcode=12)

Notice that areacode depends on zipcode which depends on city which depends on state.
Now let's add the widget to our model

class CascadingSelect(object):
    def __init__(self, *tables):
        self.tables = tables 
        self.prompt = lambda table:str(table)   
    def widget(self,f,v):
        import uuid
        uid = str(uuid.uuid4())[:8]
        d_id = "cascade-" + uid
        wrapper = TABLE(_id=d_id)
        parent = None; parent_format = None; 
        fn =  '' 
        vr = 'var dd%s = [];var oi%s = [];\n' % (uid,uid)
        prompt = [self.prompt(table) for table in self.tables]
        vr += 'var pr%s = ["' % uid + '","'.join([str(p) for p in prompt]) + '"];\n' 
        f_inp = SQLFORM.widgets.string.widget(f,v)
        f_id = f_inp['_id']
        f_inp['_type'] = "hidden"
        for tc, table in enumerate(self.tables):             
            db = table._db     
            format = table._format            
            options = db(table['id']>0).select()
            id = str(table) + '_' + format[2:-2]             
            opts = [OPTION(format % opt,_value=opt.id,
                                 _parent=opt[str(parent)] if parent else '0') \
                                  for opt in options]
            opts.insert(0, OPTION(prompt[tc],_value=0))
            inp = SELECT(opts ,_parent=str(parent) + \
                                  "_" + str(parent_format),
                                  _id=id,_name=id,
                                  _disabled="disabled" if parent else None)
            wrapper.append(TR(inp))
            next = str(tc + 1)
            vr += 'var p%s = jQuery("#%s #%s"); dd%s.push(p%s);\n' % (tc,d_id,id,uid,tc)            
            vr += 'var i%s = jQuery("option",p%s).clone(); oi%s.push(i%s);\n' % (tc,tc,uid,tc)
            fn_in = 'for (i=%s;i<%s;i+=1){dd%s[i].find("option").remove();'\
                    'dd%s[i].append(\'<option value="0">\' + pr%s[i] + \'</option>\');'\
                    'dd%s[i].attr("disabled","disabled");}\n' % \
                           (next,len(self.tables),uid,uid,uid,uid)
            fn_in +='oi%s[%s].each(function(i){'\
                    'if (jQuery(this).attr("parent") == dd%s[%s].val()){'\
                    'dd%s[%s].append(this);}});' % (uid,next,uid,tc,uid,next)            
            fn_in += 'dd%s[%s].removeAttr("disabled");\n' % (uid,next)
            fn_in += 'jQuery("#%s").val("");' % f_id
            if (tc < len(self.tables)-1):
                fn += 'dd%s[%s].change(function(){%s});\n' % (uid,tc,fn_in) 
            else:
                fn_in = 'jQuery("#%s").val(jQuery(this).val());' % f_id
                fn += 'dd%s[%s].change(function(){%s});\n' % (uid,tc,fn_in)
                if v:
                    fn += 'dd%s[%s].val(%s);' % (uid,tc,v)                       
            parent = table
            parent_format = format[2:-2]

        wrapper.append(f_inp)
        wrapper.append(SCRIPT(vr,fn))
        return wrapper

Using the widget

Let's instantiate the widget with the dependent fields in order from least to most specific.

cascade = CascadingSelect(db.state,db.city,db.zipcode,db.areacode)

You can customize the prompt per field. Here I will account for using the proper article if the field starts with a vowel.

cascade.prompt = lambda table: "Pick "  + ("an " if str(table)[0] in 'aeiou' else "a ") + str(table)

Now apply it to our area code reference field in the phone table

db.phone.areacode.widget = cascade.widget

Test it out in a controller

def index():
    form = SQLFORM(db.phone)  
    if form.accepts(request.vars,session):
        response.flash = "Got it"
    elif form.errors:
        response.flash = str(form.errors) 
    return dict(form=form)

alt text

There you have it. The widget requires each table to have a single parameter format attribute included in the define_table function. The actual field value is stored in a hidden field and populated via javascript.

Related slices

Comments (11)

  • Login to post



  • 0
    pydev 6 years ago

    2.) A testview should lool like this:

    {{extend 'layout.html'}}
     
    <h1>Test for Cascade</h1>
    <h3>{{=form}}</h3>
     
    Important to extend the layout.html->web2py_ajax.html-> loads jQuery
    Then the CascadingDropDownMenu look like this

  • 0
    pydev 6 years ago

    Hi,

    thanks for sharing this code and saving us time!

    Playing around with it i noticed the following:

    1.) state, value,number are "ALL" reserved SQL/NOSQL keyword 

    and therefor should be renamed

     


  • 0
    domdom 8 years ago
    Hi, Great widget. I had to spend some time to understand the process. Nice ! There is work behind the code... How can I use it with a simple form helper FORM ? If you could help me, that would be nice... Dom

  • 0
    edge 8 years ago
    Hi, Great widget. Only problem I'm having with widget is when there are large number of records avialable. it can take around 15 sec for the page to load (cascading 4 related tables in total - last one having around 6700 records). With memchache enabled, this is lowered to around 13 sec. this is still a bit long for my liking. If my understanding is correct, is it possible to modify the code so that records from the last table are queried only when top tables' option are selected first? this should fetch much lower number of records and thus make it alot faster. Just my 2 cent Cheers IK
show more comments

Hosting graciously provided by:
Python Anywhere