only on the db part. select(distinct=...) sends a different query to the db
than select(). It's not web2py that "uniquify" the sets of the record
returned to make it distinct.
Then.....
It's true that doing a distinct on a million rows table forces the db to
scan through a million of rows but doing distinct
a) the db doesn't need to return one million of rows to your program ("wire
transfer" from db to app is less bulky)
b) your program doesn't need to load it into memory and transform it in a
SELECT widget
Last but not least.....web2py hasn't an API to create indexes on the db,
but indexes on a db are a powerful/somewhat required thing to do/think
about when designing large applications. If you put an index in the table
column you're requesting that distinct, the db scans only the index and not
the million rows table
On Thursday, November 1, 2012 8:18:50 PM UTC+1, Lamps902 wrote:
>
> Regarding the first point, the concern is not that the selector (as
> defined in the DB model) is instantiated with a huge variety of fields. If
> I'm not mistaken, if the "distinct" keyword in the select() function is
> left out, the program literally goes through every entry in the DB that
> matches the search criteria. I suspect that it does the same thing when
> "distinct" is used, and adds the additional step of filtering them down to
> the unique entries. I'm guessing this process may be quite resource
> intensive for a large DB. If there were a way to get the list of possible
> selector options straight from the model definition, that would seem to be
> much more efficient way of doing it (with the minor downside that there may
> not yet be any DB entries that match a given selector option), but I
> haven't found a way to do this.
>
> I guess I may need to start a new thread for the second point - might
> bring up some good tips on how to customize the grid. Thanks for the
> suggestion about the widget; I'll check it out. And thanks again for all
> the help - saved a tremendous amount of time on this task, and I'm sure
> others will find it useful as well!
>
> -Lamps
>
>
> On Thursday, November 1, 2012 12:44:26 PM UTC-4, Jim S wrote:
>>
>> Lamps
>>
>> Wish I could be more help with your questions. Here is my take.
>>
>> 1. If you are thinking about having a large list, I would try to avoid
>> that. Maybe your DISTINCT keyword will keep the list small. My thoughts
>> are that a long list (25 entries or more) is not very user friendly and if
>> I had that situation, I'd just use the search box and filter on the fields
>> base on the text entered.
>>
>> 2. I don't see how you'd be able to do this very easily. Can probably
>> be done, but you'd have to get into /gluon/sqlhtml.py and make some changes
>> there to implement.
>>
>> 3. I think you could probably do this. If it were me doing it I'd look
>> into the suggest_widget plugin available from
>> http://dev.s-cubism.com/plugin_suggest_widget.
>>
>> Best of luck! Sorry I couldn't be more helpful.
>>
>> -Jim
>>
>> On Thu, Nov 1, 2012 at 11:35 AM, Lamps902 <dhea...@gmail.com> wrote:
>>
>>> I've followed Jim's model in creating my search filter, and it appears
>>> to be working beautifully! Just have some questions that I would like to
>>> ask. First - I slightly modified the selector code so that entries show up
>>> as unique, by adding the "distinct" clause and ordering alphabetically by
>>> category, by doing the following:
>>>
>>>
>>> category_list = db().select(db.t_files.f_category, distinct=True,
>>> orderby=db.t_files.f_category)
>>> options = [OPTION(category_list[i].f_category,
>>> _value=str(category_list[i].f_category)) for i inrange
>>> (len(category_list))]
>>>
>>> Will this become extremely inefficient and slow, and grind the system to
>>> a halt as the database become large?
>>>
>>> The second question concerns an important cosmetic change - is there a
>>> good way to put the filter input fields (no labels) under the column
>>> headers/labels in the grid?
>>>
>>> Finally, if you wanted to redesign the filters to update after a certain
>>> number of characters have been modified in an input field (i.e. without
>>> having to click search), how would you go about doing that?
>>>
>>> Thanks again!
>>>
>>> -Lamps
>>>
>>> On Wednesday, October 31, 2012 7:56:27 PM UTC-4, Jim S wrote:
>>>
>>>> I'm attaching a quick-n-dirty search example. You'll have to provide
>>>> your own data to get it running. It worked with my test data. Let me
>>>> know
>>>> if you have troubles and I'll be glad to work through them with you.
>>>>
>>>> -Jim
>>>>
>>>>
>>>> On Wed, Oct 31, 2012 at 8:53 AM, Lamps902 <dhea...@gmail.com> wrote:
>>>>
>>>>> Hi, Jim. It would be great if you could provide more details and some
>>>>> code illustrating how you went about doing it. Thanks!
>>>>>
>>>>> -Lamps
>>>>>
>>>>>
>>>>> On Wednesday, October 31, 2012 9:39:24 AM UTC-4, Jim S wrote:
>>>>>>
>>>>>> Using .smartgrid, I override the default filtering capabilities and
>>>>>> provide my own. See attached screenshot. Let me know if you want more
>>>>>> details. I think I uploaded a small example app to the list some time
>>>>>> ago
>>>>>> and should be able to find it or recreate it if you like.
>>>>>>
>>>>>> -Jim
>>>>>>
>>>>>> On Wednesday, October 31, 2012 8:33:52 AM UTC-5, Lamps902 wrote:
>>>>>>>
>>>>>>> Hi, Johann - I know you can build a complex query using the search
>>>>>>> box, but that is far from intuitive for most users. I'd like to
>>>>>>> implement
>>>>>>> the same look/feel/usability as was available in webgrid, and as is
>>>>>>> shown
>>>>>>> in the image. Is there a reasonable way to do this? Thanks.
>>>>>>>
>>>>>>> -Lamps
>>>>>>>
>>>>>>> On Wednesday, October 31, 2012 2:40:05 AM UTC-4, Johann Spies wrote:
>>>>>>>>
>>>>>>>> You can do that by building a complex query using the search box.
>>>>>>>>
>>>>>>>> Regards
>>>>>>>> Johann
>>>>>>>>
>>>>>>>>
>>>>>>>> --
>>>>>>>> Because experiencing your loyal love is better than life itself,
>>>>>>>> my lips will praise you. (Psalm 63:3)
>>>>>>>>
>>>>>>>> --
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>> --
>>>
>>>
>>>
>>>
>>
>>
--
