Grid sorting questions (referenced fields and case sensitive)

[João Matos]

Hello,

I have these 2 tables opt and opt_cat, where opt depends on opt_cat for the category name and other settings.

db.define_table('opt',
                Field('name', 'string', label=T('Name'), length=NAME_LEN, notnull=True,
                      required=True, unique=True),
                Field('opt_cat_id', 'reference opt_cat', label=T('Category'),
                      notnull=True, required=True),
                auth.signature,
                format='%(name)s',
                )

db.define_table('opt_cat',
                Field('name', 'string', label=T('Name in Portuguese'), length=NAME_LEN,
                      notnull=True, required=True, unique=True),
                Field('name_en', 'string', label=T('Name in English'),
                      length=NAME_LEN, notnull=True, required=True, unique=True),
                Field('mandatory', 'boolean', default=False,
                      label=T('Mandatory'), notnull=True, required=True),
                Field('one_opt_only', 'boolean', default=False,
                      label=T('One option only'), notnull=True, required=True),
                auth.signature,
                format='%(name)s',
                )

I was testing grid sort and found that a grid like this

        table = db.opt

        grid = SQLFORM.grid(
            table,
            csv=False,
            details=False,
            maxtextlength=GRID_COL_LEN_FOR_TEXT,
            ondelete=on_delete,  # Grid only.
            onvalidation=on_validation,  # Form only.
            orderby=db.opt.name,
            paginate=session.auth.user.pagination,
            # represent_none='',  # Grid and view form only.
        )

will sort by opt_cat_id and not by the opt_cat.name that is shown.

The only solution I've found is to make a join between the 2 columns and show the client.name directly.

        query = db.opt.opt_cat_id == db.opt_cat.id

        grid = SQLFORM.grid(
            query,
            csv=False,
            details=False,
            field_id=db.opt.id,
            fields=[
                db.opt.name,
                db.opt_cat.name,
                db.opt_cat.name_en,
            ],
            headers={'opt_cat.name': T('Category'), 'opt_cat.name_en': T('Category')},
            maxtextlength=GRID_COL_LEN_FOR_TEXT,
            ondelete=on_delete,  # Grid only.
            onvalidation=on_validation,  # Form only.
            orderby=db.opt.name,
            paginate=session.auth.user.pagination,
            # represent_none='',  # Grid and view form only.
        )

Is there a better solution?

Another question is that I see that the sorting is ASCII based (small case after upper case).

Is there any way to make it sort without being case sensitive?

One last question. Is there any way to sort the fields names in the search fields list of a grid?

Thanks,

JM

[João Matos]

Found a kludge to sort the grid's search fields list.

    # Sort grid's search fields list.
    grid.element('#w2p_query_fields').components = sort_grid_search_fields_list(grid)

def sort_grid_search_fields_list(grid: gluon.DIV) -> List[gluon.OPTION]:
    """Sort grid's search fields list.

    :param grid: Grid.
    :return: Sorted search fields list (HTML options).
    """
    keys: List[str] = []
    elements: Dict[str, str] = {}
    for element in grid.element('#w2p_query_fields').components:  # type: gluon.OPTION
        keys.append(element.flatten())
        elements[element.flatten()] = element['_value']
    keys.sort(key=str.lower)
    return [gluon.OPTION(key, _value=elements[key]) for key in keys]