CODEWITHSUNDEEP
pythonsortingweb2py
Krishnan Trikkadeeri
Krishnan Trikkadeeri

Reputation: 27

web2py sort order in dropdown when format attribute of reference table is function

The Q&A here and here appear to indicate that the default sort order in the dropdown made by IS_IN_DB validator is determined by the format attribute of the referenced table. But in the following case, the default sort order is the 'id' of the referenced table: 

db.define_table('bank',
            Field('bank_code', 'string',
                  unique=True, required=True, label='Bank/FI Code'),
            Field('bank_name', 'string',
                  required=True, label='Bank/FI Name'),
            singular="Bank", plural="Banks",
            format='%(bank_name)s'
            )

db.bank.bank_code.requires=IS_UPPER()
db.bank.bank_name.requires=IS_UPPER()

db.define_table('bank_branch',
            Field('bank', 'reference bank', label='Bank/FI'),
            Field('branch_name', 'string', required=True, label='Branch Name'),
            format=lambda r:'%s-%s' % (r.bank.bank_code, r.branch_name)

Even though the dropdown labels display the labels returned by the lambda function of the table bank_branch, they are sorted on its id field.

It is advised here to use IS_IN_SET for such situations, but what can be the explanation for the normal behaviour of sorting on the basis of 'format' attribute getting changed when such format is done by lambda function?

Upvotes: 0

Views: 375

Answers (1)

Anthony
Anthony

Reputation: 25536

By default, when the IS_IN_DB validator generates the set of values and associated labels, it does not directly sort by the generated labels. Rather, in the database select, it specifies an ORDER BY clause that includes the fields used to generate the label. If the format attribute of the referenced table is a Python format string, the label fields are extracted from that format string in the order they appear. This has the effect of ordering the final set by the labels in that case.

However, if the format attribute of the referenced table is a function, IS_IN_DB does not know which fields are needed to generate the labels, so it simply selects all fields in the table and orders by all fields (in the order they appear in the table definition). In this case, because db.bank_branch.id is the first field in the table definition (though not defined explicitly), that is the first field in the ORDER BY clause, resulting in the options being ordered by the IDs of the db.bank_branch table.

If you want to force the options to be sorted by the generated labels, you can use the sort argument:

IS_IN_DB(db, 'bank_branch.id', db.bank_branch._format, sort=True)

As an aside, keep in mind that if there are many bank branches, this method of generating labels is somewhat inefficient, as the format function includes a recursive select (i.e., r.bank.brank_code), which does a separate select for every item in the list. An alternative would be to generate your own set of values and labels based on a join query and then use the IS_IN_SET validator (or use IS_IN_DB just for the validation, and specify the form widget and its options separately). Of course, at some point, there may be more branches than would be reasonable to include in a select input, in which case, you can use IS_IN_DB to do the validation but should use an alternative input widget (e.g., an Ajax autocomplete).

Upvotes: 1

Related Questions