Customizing generated columns from SQLFROM.grid

[Francisco Ribeiro]

hello everyone,

I believe I have ran into a dead end but maybe one of you may find a way for what I'm trying to do. So, please consider this model:

db.define_table('house',

    Field('location_type', requires=IS_IN_SET(['city', 'country', 'sea'])),

    Field('location_type_msg'),

    Field('capacity', requires=IS_IN_SET(['single', 'couple', 'small family', 'big family']),

    Field('capacity_msg'))

you can see the pattern I used in the field names, "fieldname" followed by "fieldname_msg".

Now, what I want to do is to generate a grid with only two columns "location_type" and "capacity" where "location_type_msg" and "capacity_msg" would be accessible once you mouse hover  through their respective fields.

Example:

id | location_type | capacity

===========================

1  |     city             |       single

2  |     sea             |     small family

now if i go over the word 'city' I would see something like "New York" or "London" and if I mouse hover "small family" i would see something like "90 square meters"

hope it makes sense.

Thank you,

Kind regards

Francisco

[Francisco Ribeiro]

child's play!

Answer to my own question:

======================

on the db file, add the following:

def get_column_index(grid, colname):
    colname = colname.replace('.','-')
    for col in grid[2][0][0][0]:
        if col['_id'] == colname:
            return col['data']['column']-1
    return -1


def apply_title_to_column(grid, title_list, column_number):
    for cell,title in zip(grid[2][0][0][2], title_list):
        if title is not None and str(title) != "<td></td>" and str(title) != "<td>None</td>":
            cell[column_number]=TD(cell[column_number][0],_title=title)


def get_table_rows(grid):
    return grid[2][0][0][2]


def get_column(grid, column_number):
    return [row[column_number][0] for row in get_table_rows(grid)]


def remove_grid_column(grid, column_number):
    del grid[2][0][0][0][column_number]
    del grid[2][0][0][1][0][column_number]
    for row in get_table_rows(grid):
        del row[column_number]


def add_titles(grid):
    cols_to_delete=[]
    if grid.rows:
        for colname in grid.rows.colnames:
            if colname.endswith('_msg'):
                column_index = get_column_index(grid, colname)
                cols_to_delete.append(column_index)
                title_list = get_column(grid, column_index)
                apply_title_to_column(grid, title_list,  get_column_index(grid, colname[:-4]))
        for col_number in reversed(cols_to_delete):
            remove_grid_column(grid, col_number)
    return grid

# then on the controller you can have something like:

def housegrid():

    grid=SQLFORM.smartgrid(db.house)
    return add_titles(grid)

# view code:

{{=LOAD('default','housegrid.load', ajax=True)}}

Notes:

• this code is outrageous, egregious, preposterous but it works and it's quite modular if you want to use it for other changes like this.
  
• one nice detail about this is that you can still make all your search queries, including within the fields that are now only being shown on mouse hovering 
  
• an alternative approach would be to use jQuery manipulations in the DOM but this looks a lot more resilient and decoupled from the view side of things
  
• obviously, this uses internals that can be modified at any time so backward compatibility can not be assured even though this should be very rare.
  
• it would be nice to have an API for grids. I think there is this big "jump" from database records to generated HTML code ready to be rendered, not so much control over the Application logic on it.
  

Kind regards,

Francisco 

[tim.n...@conted.ox.ac.uk]

Or, on the slightly simpler side:

before you call SQLFORM.grid():

from gluon import TAG

db.house.location_type.represent = lambda value, row: TAG.abbr(value, _title=row.location_type_msg)

db.house.capacity.represent = lambda value, row: TAG.abbr(value, _title=row.capacity_msg)