how to validate field that depend on each other in the model rather than form

[Hadi Sunyoto]

from: http://www.web2py.com/books/default/chapter/29/07/forms-and-validators#Database-validators

Validators with dependencies

Usually validators are set once for all in models.

Occasionally, you need to validate a field and the validator depends on the value of another field. This can be done in various ways. It can be done in the model or in the controller.

There is an example validation done in controller but there is no example validation done in model

My table:

db.define_table('config',
    Field('config_name', 'string', length=255, required=True, unique=True),
    Field('convert_option', 'string', length=255,
        requires=IS_IN_SET(CONVERSION, zero=None)),
    Field('config_value', 'string', length=255, required=True),
    Field('default_value', 'string', length=255, required=True))

I want to validate if config_value is greater than default value (for example), but i don't want to do it in SQLFORM or FORM.

or is it a bad idea to put validation that depend on other fields in "model" rather than form?

Thank you

Hadi

[mcamel]

I'm wondering just the same. How can this be done in model?.

Particularly I want to use IS_IN_DB on a referenced field based on a query filtering by the value of another field of the table.

I've tried this but didn't work:  

db.table1.field1.requires = lambda x,row: IS_IN_DB(db(db.table2.field2==row.field2)), 'table2.id')

Nor did this one:

db.table1.field1.requires = IS_IN_DB(db(db.table2.field2==db.table1.field2)), 'table2.id')

where field1 is field of table1: 

Field('field1', 'reference table2')

Regards.

[Anthony]

On Monday, September 16, 2013 at 2:49:08 AM UTC-4, Hadi Sunyoto wrote:

There is an example validation done in controller but there is no example validation done in model

My table:

db.define_table('config',
    Field('config_name', 'string', length=255, required=True, unique=True),
    Field('convert_option', 'string', length=255,
        requires=IS_IN_SET(CONVERSION, zero=None)),
    Field('config_value', 'string', length=255, required=True),
    Field('default_value', 'string', length=255, required=True))

I want to validate if config_value is greater than default value (for example), but i don't want to do it in SQLFORM or FORM.

or is it a bad idea to put validation that depend on other fields in "model" rather than form?

You could use request.post_vars in the validator. For example, assuming these values are integers:

Field('config_value', ...,
      requires=IS_EXPR(lambda value: value > int(request.post_vars.default_value)))

Assuming these fields store numbers, you should use the appropriate field type (integer, double, decimal) rather than string.

Anthony

[mcamel]

Hi Anthony,

Thanks for your reply.

What i want to acheive is to restrict IS_IN_DB validator depending on the value of another field. It can be done on controllers, but i couldn't do it on model.

Here is an example:

• We have 2 departments: Letters and Numbers
• We have teachers assgined to only one department
• We have pupils assigned to only one department and optionally to a teacher, so we want to restrict the list of possible teachers to the department in common

   

def index():
    db = DAL('sqlite:memory:')
    db.define_table('department',
        Field('name'), format='%(name)s')
    db.define_table('teacher',
        Field('department_id', 'reference department'),
        Field('name'))
    db.define_table('pupil',
        Field('department_id', 'reference department'),
        Field('teacher_id', 'reference teacher'),
        Field('name'))
    # populating...
    if not db(db.department).count():
        db.department.bulk_insert([dict(id=1, name='Letters'), dict(id=2, name='Numbers')])
    if not db(db.teacher).count():
        db.teacher.bulk_insert([dict(name='TeacherA', department_id=1), dict(name='Teacher1', department_id=2)])
    if not db(db.pupil).count():
        db.pupil.bulk_insert([dict(name='PupilA', department_id=1), dict(name='Pupil1', department_id=2)])
   
    db.pupil.teacher_id.requires = IS_EMPTY_OR(IS_IN_DB(db((db.teacher.department_id==db.pupil.department_id)),
                                                        'teacher.id', '%(name)s', zero=T('Choose one')))
    form = SQLFORM.grid(db.pupil, user_signature=False)
   
    return dict(form=form)

This doesn't work as intended becuase it lets you assign teachers from both departments. I guess i'm doing a cartesian product instead of a filter...

If you replace 

(db.teacher.department_id==db.pupil.department_id)

with

(db.teacher.department_id==request.post_vars.department_id)

then you have an empty teachers list. Probably because post_vars is empty when you load the form?.

If you try to use lambda as you can do with 'represent', then something weird happens and the dropdown dissapears:

    db.pupil.teacher_id.requires = lambda x,row: IS_EMPTY_OR(IS_IN_DB(db((db.teacher.department_id==row.department_id)),
                                                        'teacher.id', '%(name)s', zero=T('Choose one')))

Maybe this cannot be done in model.

Regards!

[Anthony]

If you think about it, this is not logically possible -- how can you set values for the teacher dropdown that depend on the selected department before the department has even been selected? Instead, you have to handle this via Javascript -- once the user selects the department, then you populate the list of teachers. There is no built-in support for that functionality, but for some ideas, see http://stackoverflow.com/questions/8146260/best-practice-for-populating-dropdown-based-on-other-dropdown-selection-in-web2p/8152910#8152910.

Anthony

[mcamel]

You are right. I planned to use validator for the "initial" state and then Ajax for "online changes", but it was too messy, so finally i'm using just Ajax.

Thanks for the info links but I've written my own solution. Here it is, just in case it helps anyone:

Moved db definition code to a model, and add this after grid definition:

    if 'edit' in request.args:
        # Ajax department_id -> teacher_id
        # 1.- Initial filter
        form += SCRIPT(''' jQuery(document).ready(function(){
               ajax('%s', ['department_id', 'teacher_id'], 'pupil_teacher_id');
            }); ''' % URL('options_teacher'))
        # 2.- Ajax modification setted by DOM
        department_id = form.element('select #pupil_department_id')
        department_id['_onchange'] = "ajax('%s', ['department_id'], 'pupil_teacher_id')" % URL('options_teacher')

Here the function that returns the filtered OPTIONs:

def options_teacher():
    T = db.teacher
    rs = db(T.department_id == request.vars.department_id).select(T.ALL, orderby=T.name)
    teachers = [OPTION(r.name, _value=r.id, _selected=bool(r.id == int(request.vars.teacher_id or 0))) for r in rs]
    return CAT(OPTION(T('Choose One'), _value=''), *teachers)

Optional additional check can be introduce adding to grid definition:

onvalidation=lambda form: my_onvalidation(form)

definig previously:

    def my_onvalidation(form):
        if form.vars.department_id and form.vars.teacher_id \
        and db.teacher[form.vars.teacher_id].department_id != int(form.vars.department_id):
            form.errors.teacher_id = T('Error')

Regards!

[Enrique Almeida Maldonado]

Hello, I had the same problem and I've just found an elegant solution for it. First, you neet to notice that before any validation, the values of the fields travel to the model, so, in the model you can manipulate them throug the request.vars . After that everything else is easy: Just create a validator of your own, extract the values of the fields and use them as you like. An example will do better:

Let's supose you have a table with two fields:

dbOBJECT.define_table("example",

                      Field("high_number",

                            "integer",

                            label= T('High number'),

                            requires=[IS_NOT_EMPTY(T(stringBuilder.emptyField))]),

                      Field("other_number",

                            "integer",

                            label= T('Lower number'),

                            requires=IS_NOT_EMPTY(T(stringBuilder.emptyField))))

And you need the second number to be lower than the first. For explanation purposes let's create a custom validator

class VALIDATE_NUMBERS(object):

    def __init__(self, error_message="Error, this value can't be greather than the above one"):

        self.error_message = error_message

    def __call__(self, value):

        error = None

        high_number=request.vars['high_number']

        if high_number < value:

            error = self.error_message

        return (value, error)

Well, the other thing to do is include this validation on the model:

dbOBJECT.define_table("example",

                      Field("high_number",

                            "integer",

                            label= T('High number'),

                            requires=[IS_NOT_EMPTY(T(stringBuilder.emptyField))]),

                      Field("other_number",

                            "integer",

                            label= T('Lower number'),

                            requires=IS_NOT_EMPTY [(T(stringBuilder.emptyField)), VALIDATE_NUMBERS()]))

The magic is done!!! Enjoy...

El lunes, 16 de septiembre de 2013, 8:49:08 (UTC+2), Hadi Sunyoto escribió: