Trying to use dict to filter values for a 2nd dropdown list based on the value of 1st dropdown list

[Dr. Bill Y.K. Lim]

Hi, I am new to Python and web2py and I am not sure if my approach below is correct - in any case I have a problem with it:

I have added 2 extra fields in db.py as follows:

from applications.myapp.modules.countriesandcities import *
auth.settings.extra_fields['auth_user']=[
   Field('country'),
   Field('city')]

db.auth_user.country.requires = IS_IN_SET(COUNTRIESANDCITIES.keys())   # this is working as expected

db.auth_user.city.requires = IS_IN_SET(COUNTRIESANDCITIES[request.post_vars.country])    # this is not working - getting KeyError

I created a module called countriesandcities.py (shortened here for illustration):

COUNTRIESANDCITIES = {'Australia':["Adelaide", "Brisbane", "Perth", "Melbourne"], 'Malaysia': ["Ampang", "Kuching", "Miri", "Kuala Lumpur"]}

What I am trying to achieve is this:

(1) When user is registering, user will select the 'country' from a drop down list which is required to be one of the keys in the COUNTRIESANDCITIES dict. This part works fine.

(2) Based on the selection in (1) above, the user will now select the 'city' from a drop down list and this drop down list should just show the 'values' (cities) for the 'key' (country) previously selected.

However, part (2) doesn't seem to be working and I am getting this error:

<type 'exceptions.KeyError'>(None)

That seems to suggest that there is no existing 'key' which would seem to imply that   request.post_vars.country  is not working.

Any help would very much be appreciated.

[Massimo Di Pierro]

It is not working because you need the list of options when the form is generated, not when it is submitted, in fact this line is executed when the form is generated:

db.auth_user.city.requires = IS_IN_SET(COUNTRIESANDCITIES[request.post_vars.country])  

yet at this time the form is not submitted yet and request.post_vars.country is None.

You cannot do it this way. Conditional options require some JS programming. Something like this:

http://remysharp.com/2007/01/20/auto-populating-select-boxes-using-jquery-ajax/

but with web2py not PHP.

[Kiran Subbaraman]

Maybe this will help: http://www.web2pyslices.com/slice/show/1724/cascading-dropdowns-simplified ?

________________________________________
Kiran Subbaraman
http://subbaraman.wordpress.com/about/

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Leonel Câmara]

You are in luck, I had to do this for one of my projects. Here's a widget I made for a project where I had the same problem but I had 3 levels, Country->state->city

For countries that don't have states you can use administrative regions, but it's also trivial to adapt this to only 2 levels.

MODELS

db.define_table('country',
    Field('name', length=128, requires=IS_NOT_EMPTY(), notnull=True, unique=True),
    Field('acronym', length=2, requires=IS_NOT_EMPTY()),
    format='%(name)s'
)

db.define_table('state',
    Field('name', length=128, requires=IS_NOT_EMPTY(), notnull=True),
    Field('acronym', 'string', requires=IS_NOT_EMPTY()),
    Field('country','reference country', requires=IS_IN_DB(db, db.country.id, '%(name)s'), represent=lambda x, row: db.country[x].name),
    format='%(name)s'
)

db.define_table('city',
    Field('name', length=128, requires=IS_NOT_EMPTY(), notnull=True),
    Field('state', 'reference state', requires=IS_IN_DB(db, db.state.id, '%(name)s'), represent=lambda x, row: db.state[x].name),
    format='%(name)s'
)

auth.settings.extra_fields['auth_user']= [
    Field('city','reference city', label=T('City'), requires=IS_IN_DB(db, db.city.id, '%(name)s'))
]


class LOCATION_SELECTS(DIV):
    """ A helper that essentially creates a div with the 3 selects used to choose a location """

    def __init__(self, *args, **attributes):
        if '_id' not in attributes:
            import random
            attributes['_id'] = 'ls' + str(random.random())[2:]

        def mk_sel_select(entities, selected, name):
            s = SELECT(_name=name)
            s.append(OPTION(T('Pick a %s' % name), _value=0))
            for entity in entities:
                if entity.id == selected:
                    s.append(OPTION(entity.name, _value=entity.id, _selected='selected'))
                else:
                    s.append(OPTION(entity.name, _value=entity.id))
            return s

        DIV.__init__(self, *args, **attributes)
        countries = db(db.country.id > 0).select(orderby=db.country.name)

        if 'initial_city' in attributes:
            sel_city = attributes['initial_city']
            rec_city =  db.city[sel_city]
            sel_state = rec_city.state
            sel_country = rec_city.state.country
            states = db(db.state.country == sel_country).select(orderby=db.state.name)
            cities = db(db.city.state == sel_state).select(orderby=db.city.name)

            self.components.append(mk_sel_select(countries, sel_country, 'country'))
            self.components.append(mk_sel_select(states, sel_state, 'state'))
            self.components.append(mk_sel_select(cities, sel_city, 'city'))
        else:
            self.components.append(SELECT(OPTION(T('Pick a country'), _value=0), *[OPTION(country.name,_value=country.id) for country in countries], _name='country'))
            self.components.append(SELECT(OPTION(T('Pick a region'), _value=0), _name='state', _disabled='true'))
            self.components.append(SELECT(OPTION(T('Pick a city'), _value=0), _name='city', _disabled='true'))

    def xml(self):
        return (DIV.xml(self) + 
            SCRIPT("""$(document).ready( function() {
                        var country_sel = $('#%(sid)s select[name="country"]');
                        var state_sel = $('#%(sid)s select[name="state"]');
                        var city_sel = $('#%(sid)s select[name="city"]');
                        var state_cache = new Object();
                        var city_cache = new Object();

                        var state_func = function(data) {
                            state_sel.children('option:gt(0)').remove();

                            $.each(data.states, function(key, val) {
                                state_sel.append('<option value="' + data.states[key].id + '">' + data.states[key].name + '</option>');
                            });

                            state_sel.removeAttr('disabled');                        
                        }

                        var city_func = function(data) {
                            city_sel.children('option:gt(0)').remove();

                            $.each(data.cities, function(key, val) {
                                city_sel.append('<option value="' + data.cities[key].id + '">' + data.cities[key].name + '</option>');
                            });

                            city_sel.removeAttr('disabled');
                        }

                        country_sel.change(function () {
                            var val = country_sel.val();
                            if (val === '0') {
                                state_sel.children('option:gt(0)').remove()
                                state_sel.attr('disabled', '');
                                city_sel.children('option:gt(0)').remove();
                                city_sel.attr('disabled', '');
                                return;
                            }
                            city_sel.children('option:gt(0)').remove();
                            city_sel.attr('disabled', '');
                            
                            if (val in state_cache) {
                                state_func(state_cache[val]);
                            } else {
                                $.getJSON('%(getstates)s' + '/' + encodeURIComponent(val), function(data) {
                                    state_cache[val] = data;
                                    state_func(data);
                                });
                            }
                        });

                        state_sel.change(function () {
                            var val = state_sel.val();
                            if (val === '0') {
                                city_sel.children('option:gt(0)').remove();
                                city_sel.attr('disabled', '');
                                return;
                            }
                            if (val in city_cache) {
                                city_func(city_cache[val]);
                            } else {
                                $.getJSON('%(getcities)s' + '/' + encodeURIComponent(val), function(data) {
                                        city_cache[val] = data;
                                        city_func(data);
                                });
                            }
                        });
                });
                """ % {'sid':self['_id'], 'getstates':URL('default', 'get_states.json'), 'getcities':URL('default', 'get_cities.json')} ).xml())


def city_widget(field, value):
    """ A widget for city fields that makes you put the country and state too """
    try:
        value = int(value)
        if value <= 0:
            raise ValueError()
        widget = LOCATION_SELECTS(initial_city=value)
    except (ValueError, TypeError) as e:
        widget = LOCATION_SELECTS()

    widget.components[-1]['requires'] = field.requires
    return widget

db.auth_user.city.widget = city_widget

CONTROLLERS (default.py)

def get_states():
    """ 
    Get the states for a given country id, these might be districts 
    or whatever makes sense for the country in question.
    """
    session.forget()
    result = db(db.state.country == request.args(0)).select(db.state.id, db.state.name, orderby=db.state.name).as_list()
    return {'states': result}


def get_cities():
    """ 
    Get the cities for a given state id.
    """
    session.forget()
    result = db(db.city.state == request.args(0)).select(db.city.id, db.city.name, orderby=db.city.name).as_list()
    return {'cities': result}

Make sure you allow generic.json for the 2 controllers. And it is done.

[Derek]

aaah you are creating your view in the model, have you not heard of MVC and separation of concerns?

That would work, but it is violating the MVC principle. 

Also, it looks like you are only storing the 'city' and not the country or state. What happens if in two countries they have the same city? (I can think of one off the top of my head - Moscow, Fl, USA, and Moscow, Russia). You won't know which country the person is from...

I can also think of several cities in different states. "Bowling Green" is a common city name. It's a city in Florida, Indiana, Kentucky, Maryland, Missouri, Ohio, South Carolina, and Virgina (among others).

My way would do it (http://www.web2pyslices.com/slice/show/1724/cascading-dropdowns-simplified) but I don't think it's ever been added to an add account mechanism before.

[Leonel Câmara]

I'm not explicitly storing the country or state because the city has a reference to a state which has a reference to the country.

I don't think I'm breaking MVC much more than it is already routinely broken in web2py whenever you use SQLFORM, as the states and countries on the dropdown have their controller functions and LOCATIONS_SELECT doesn't break MVC any more than any of the helpers or widgets already included in web2py. If it burns your eyes for it to be in a model file, just put it in a module (you could call it custom_widgets.py or something) in the modules folder of your application. My point is, it is no more breaking of MVC than importing any widget in your model.