+1
this would be a great and needed feature... just stumbled into this
problem, and working around it :)
On Friday, August 31, 2012 5:48:17 PM UTC-4, Massimo Di Pierro wrote:
>
> Hello Mike,
>
> thanks for your patch? Would you please attach it to a google code issue
> so we do not forget. We cannot add new feaures until all 2.0.x issues have
> been resolved.
>
> On Friday, August 31, 2012 2:41:30 PM UTC-5, Mike Leone wrote:
>>
>> 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.
>>
>
--
