Cascading Drop Down Lists

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)

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.

Comments (11)

Login to post
order by: newest oldest upvoted downvoted
0
tom 14 years ago
How can I save the 'zipcode' in DB Phone also ? Here is a demonstration of the db: ------- id.phone zipcode.phone areacode.phone regards
0
mrfreeze 14 years ago
Not sure I understand. There's no need to store it in the phone table. You can get to the zipcode of a db.phone record by doing:
```
phn = db(db.phone.id>0).select().first()
zip = phn.areacode.zipcode.value
```
0
tom 14 years ago
I want to store it for another project. This is only an example db.define_table('phone', Field('zip',writable=False,readable=False) Field('areacode',db.areacode), def index(): form = SQLFORM(db.phone) if form.accepts(request.vars,session, dbio=False): form.vars.zip= ?? zip.areacode ?? form.vars.id = db.phone.insert(**dict(form.vars)) response.flash = 'record inserted' .... How can I do it ? thanks
0
mrfreeze 14 years ago
Everything you need should be in request.vars
0
tom 14 years ago
when one of the fields needs to be computed from the value of other input fields : Error Message: invalid field names: ['areacode_value', 'zipcode_value']
0
richard 13 years ago
Hello, I have a peculiar need for cascading because of a business rule one of the linked elements could be linked to one of two ancestor at a time but there is both kind of hierarchy : folder > volume > tome > sheet or folder > tome > sheet I will try to figure out how I can modify it to make it handling this when creating the instance : cascade = CascadingSelect(db.state,db.city,db.zipcode[db.state, db.city],db.areacode)
0
edge 13 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
0
domdom 13 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
pydev 11 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
pydev 11 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 11 years ago

Setting up

Using the widget

Related slices

Comments (11)