patch to make Rows.setvirtualfields work with SQLTABLE

[Ivan Matveev]

----motivation:
It wold by nice to have a tool to add arbitrary columns to
db().select() result
to be able to create multiple views of the same tables(including
joins).
That wold be especially valuable when dealing with legacy databases
when you can't design the database according to the web user
interface.

The easiest solution wold be something like:

class ExtraField:
def new_column(self):
if self.some_field_in_select==' something':
return A('some_action_link', _href=URL(
f=some_action_controller_function,
args=[self.id]))
#if select contains 'as' it shell be
'self.as.id'
#if select contains join it shell be
'self.table.id'
#'id' is just an example, any othe
field can be
#used
else:
return A('great thing', _href='http://www.web2py.com')

rows=db(db.some_table).select()
rows.colnames.append('some_table.new_column')
rows.setvirtualfields(some_table=ExtraField())
table=SQLTABLE(rows)

This will give an exception telling that
sqlhtml.py:SQLTABLE.__init__ can't do
field = sqlrows.db[tablename][fieldname]
because there is no 'new_column' in the database.
It wants to get the field from the db model to know how to render it.
This is doesn't matter when we add a new field to select result
because it shell(and will by a view) be cast to string.

----The patch:
change
sqlhtml.py:SQLTABLE.__init__
field = sqlrows.db[tablename][fieldname]
to
try:
field = sqlrows.db[tablename][fieldname]
except:
field = None

change
sqlhtml.py:SQLTABLE.__init__
if field.represent:
r = field.represent(r)
to
if not field:
pass
elif field.represent:
r = field.represent(r)

---Now you are able to include a new field in SQLTABLE
containing anything(text, link, image, button, form, whatever)
depending on row contents(ExtraField.new_column(self): self is the
Row) ,
by adding the field to db().select() result,.

---Note:
in
class ExtraField:
name of the class doesn't matter. The only time you use it is

rows.setvirtualfields(some_table=ExtraField())

then address your new column by 'some_table.new_column' while rows
object exists.

---Limitation:
in

rows.colnames.append('some_table.new_column')
rows.setvirtualfields(some_table=ExtraField())

'some_table' shell be a table mentioned in your select and present in
the db,
otherwise
'some_table' will be added as a sub dict to Row object
(IMHO this is wrong, the Row object shell be 1d always, the 'as' info
shell be in column names)
and you will not see it.

WBR
Ivan.

---Disclaimer : my understanding of web2py is not mature. For real
thing ask Massimo.

PS.
---Thank you Massimo. web2py is a great stuff. 1 month I'm learning
python and web2py is the motivation.

PPS.
Can I use <code> or <pre> or other tags to post code on the list?

[Bruno Rocha]

I think this patch needs to be reviewed, as sqlhtml.py changed a lot.

[Bruno Rocha]

This is my working code: http://snipt.net/rochacbruno/virtual-fields-in-sqltabe

Note the headers=None

So need to patch the header definition to use the virtualfieldname, or better, How to define a label for a virtual field?

for work with SQLTABLE (or plugin_datatable) we need to add the colname for every virtual field: myrows.colnames.append('task.teste')

How to append every virtual field to rows.colnames? I think this could be done in sqlhtml.py as:

for field in sqlrows.virtualfields: sqlrows.colnames.append('tablename.'+field)

Is it possible?

[Ivan Matveev]

> This is my working
> code: http://snipt.net/rochacbruno/virtual-fields-in-sqltabe
> Note the headers=None
> So need to patch the header definition to use the virtualfieldname, or
> better, How to define a label for a virtual field?

I think you can try to add label property to your MyVirtualFields class.
Like:
__init__(self):
self.label = "mylabel"

> for work with SQLTABLE (or plugin_datatable) we need to add the colname for
> every virtual field: myrows.colnames.append('task.teste')
> How to append every virtual field to rows.colnames? I think this could be
> done in sqlhtml.py as:
> for field in sqlrows.virtualfields:
> sqlrows.colnames.append('tablename.'+field)
> Is it possible?

Its late, I can be understanding you wrong.
Why you don't want to do it by hand the way it is in your code?

myrows.colnames.append('task.teste')

[Bruno Rocha]

Solution to the error:

<code file=sqlhtml.py line=1253>

elif headers=='labels':

            headers = {}

            for c in columns:

                (t,f) = c.split('.')

                try:

                    field = sqlrows.db[t][f]

                    headers[c] = field.label

                except KeyError:

                    headers[c] = ' '.join([w.capitalize() for w in c.split('.')[-1].split('_')])

</code>

I am still looking for a solution to:

1. Set labels for virtual fields (because I need to use T() in the virtual field label)
2. Include the virtual fields in sqlrows.colnames automatically

2010/12/9 Ivan Matveev <imatv...@gmail.com>

--

Bruno Rocha

http://about.me/rochacbruno/bio

[Bruno Rocha]

Now I can have labels for my virtual fields!

my_controller:

http://snipt.net/rochacbruno/virtual-fields-labels-in-sqltable

Changes I made in sqlhtml.py:

http://snipt.net/rochacbruno/changes-in-sqlhtmlpy-to-include-labels-for-virtual-fields

Now this is working just as I need!

 (just have to know if this will be included in sqlhtml.py, or if I will need to manage it by myself, or may be anyone have a better idea)