Hello,
We're building a web2py application that leverages SQLFORM.smartgrid
heavily. Out of the box, this does not support filtering on 1:M
relationships, or reference fields on the model. We understand the concerns
about this: It could potentially be a huge performance hit, especially if
the related table has a large number of rows.
On the other hand, this functionality is very valuable. Being able to
filter on related tables is a very common need in applications. Often,
users need to filter on "lookup" tables that have a relatively small number
of rows, and the performance hit is negligible. This is a very common
feature in Django, and in Rails admin interfaces like ActiveScaffold and
RailsAdmin.
Example:
Given a model like this:
db.define_table('book',
Field('user_id', 'reference auth_user'),
Field('title'),
Field('genre'))
And a controller like this:
def index():
grid = SQLFORM.smartgrid(db.book)
return dict(grid=grid)
The query interface will look like this by default:
http://i49.tinypic.com/2ugjt02.jpg
Note how this includes title and genre, but not the auth_user reference.
We patched web2py to allow to for 1:M reference fields in the query
interface. The resulting interface and generated query look something like
this:
http://i46.tinypic.com/28taq74.png
Here is our simple patch to allow searching on reference fields like this:
https://gist.github.com/ff543a5c6d3bc14b9079
This is obviously NOT ready for production; it's a simple proof of concept.
Note the potentially very expensive "ALL" query. Because of the
performance risks, something like this only makes sense if developers can
opt in, i.e. explicitly say so if they want to include a given reference
field in the query interface.
1. Would the web2py maintiners be interested in a feature like this if it
is opt-in? I'm willing to devote some time to submit a more robust patch
if I could get some guidance.
2. I also have a simple implementation for querying on list:reference
fields that I can share as well.
Finally, I just want to thank the web2py team. I've really enjoyed using
the framework and hope to help out as much as I can.
Thanks,
Mike Leone
Principal, Engineering
Panoptic Development, Inc.
--
