1. #1
    Sencha - Community Support Team jsakalos's Avatar
    Join Date
    Apr 2007
    Location
    Slovakia
    Posts
    27,557
    Vote Rating
    383
    jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future

      0  

    Default An Ideal Grid Filter for Large Databases

    An Ideal Grid Filter for Large Databases


    Hi folks!

    In my pre-Ext era I'd been developing a PHP web application that dealt with quite large databases with record count per table 10000+. The number of records is no problem for database backends, they can usually handle millions of records easily.

    The big tables are problem first for (web) developer as he cannot, let's say, generate a combo with 1000 options. Yes, he can, but what about generation time and network throughput? Second, big tables are problem for users in that that user wants quick answers to questions like: "Show me overdue invoices." and he doesn't want to go through thousands of records to find them.

    Keeping this in mind I have developed the grid filtering that I consider ideal for such purposes; my users praise it highly too. Let me describe it.

    Each grid has input fields just beneath the column labels where user can type the filter expression. The database backend (mySQL in my case) uses rlike function to filter so if user learns the syntax he can put really advanced expressions there. Expressions in individual inputs are connected by and operator in SQL where clause. See attachment 1. You can see also "Clear filter" and "Apply filter" on the right of the picture.

    If we take the invoice list as an example and we want to answer the above question we put ">0" in the "Owed sum" field and "< now()" in the "Due date" field, then click "Apply filter" and we get the list. If "Show totals" is checked we get also "Total owed sum".

    I first implemented this logic but life has shown that users usually have "Sets of question" they want answers to asked over and over. Letting users to have lists of such filters (maybe written on a paper) and let them to write these to the fields over and over had led to complaints and frustrations.

    Thus, I've developed the filter management system that allows to give a name to the filter once setup and to save it with the given name. In out example we would give filter name "Overdue Invoices" and we'd save it. Whenever we need the list we just click "Saved filters" icon on the left, click an item from the presented list and we have the answer. See attachments 2 and 3.

    The last convenience feature is that if we have relatively short list of possibilities we put the list in the (inline) popup so user can choose. In this example it is possibility to choose the "Seller". See attachment 4.

    Maybe this is not something new under the sun, moon and stars but it's concept that works for years with full user's satisfaction.

    I'd like to have it in Ext. And you?
    Attached Images
    Jozef Sakalos, aka Saki

    Education, extensions and services for developers at new http://extjs.eu
    News: Grid Search Plugin, ExtJS 5 Complex Data Binding using MVVM


  2. #2
    Sencha User jack.slocum's Avatar
    Join Date
    Mar 2007
    Location
    Tampa, FL
    Posts
    6,955
    Vote Rating
    17
    jack.slocum will become famous soon enough jack.slocum will become famous soon enough

      0  

    Default


    Rich Vista-style filters can be added pretty easily with GV3. I am planning on including an example of doing it in the Ext 2.0 examples.

    Similar to the GV3 RowExpander helper class, we may implement a Filter helper class to include in the examples and handle all the plumbing for at least the client-side filtering.
    Jack Slocum
    Ext JS Founder
    Original author of Ext JS 1, 2 & 3.
    Twitter: @jackslocum
    jack@extjs.com

  3. #3
    Sencha User
    Join Date
    Mar 2007
    Location
    Toronto, ON, CA
    Posts
    202
    Vote Rating
    0
    timb is on a distinguished road

      0  

    Default


    I have used a few different interfaces for filtering in the past, depending on the need of the client. Here are the different methods that I've used or am thinking of using in the future:
    • Inline column filtering - This is similar to the approach that jsakalos has outlined. In my apps where I used this approach, I have a funnel icon next to each filter that invokes a context menu when clicked. This menu has items such as "equal to", "not equal to", "greater than", etc. The list is specific for the data type of the field. This appears to work well for a great majority of my grids and users are pretty happy with it. Users find it a little inconsistent to mix this type of filtering with more advanced filtering.
    • Basic filtering - One textbox for input that will search one or more fields (usually the fields shown in the grid). This is less efficient on the database side than the other filter interfaces, as you have to search all of the fields every time (in my implementation). I have been playing around with the idea of allowing text like this, though: joe lastname:doe. This will search all fields for joe, and only the last name field for doe. The basic filter can be used with advanced filtering.
    • Advanced/Specialized/Custom filtering - Sometimes the client would like to have a few fields listed (almost always less than 10 in my experience). For example, they may want individual fields for First Name (textbox), Last Name (textbox), Birth Day (date picker), Status (possibly combo, list, checkboxes, etc.). This can be used with basic filtering.
    • Advanced Generic filtering - I haven't actually implemented this yet, but an advanced generic filter could be pretty useful. This would allow you to select the field, the comparison operator and the value you want to compare it to. You could do this for as many fields as you want. See Microsoft Outlook for an example. This can be used with basic filtering.

    In all cases, I've found that users really like having the ability to save the searches with a user definable name.

    Now on to my wish list.

    It would be nice to see a "filter type" option with options such as basic, advanced (generic), inline, custom and none (or undefined). For basic, it could just add a toolbar with one textbox and one or two buttons (it could be a "BasicFilterToolbar" component, for example). For Advanced (generic), this could be a component as well (I will probably write the code for this eventually, but I can't see myself doing it within the next couple of months). Custom would be done by the developer. Inline appears to be the most complicated at this point.

    Inline is really the most important type to add support for, as everything else can be done with relatively little code. Once the developer has the code to add the other types, it can easily be made into a component that can be reused.

  4. #4
    Sencha - Community Support Team jsakalos's Avatar
    Join Date
    Apr 2007
    Location
    Slovakia
    Posts
    27,557
    Vote Rating
    383
    jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future

      0  

    Default


    Quote Originally Posted by jack.slocum View Post
    Rich Vista-style filters can be added pretty easily with GV3. I am planning on including an example of doing it in the Ext 2.0 examples.

    Similar to the GV3 RowExpander helper class, we may implement a Filter helper class to include in the examples and handle all the plumbing for at least the client-side filtering.
    I'm glad to hear these good news from you.

    However, being the Linux guy, I have no clue what are Vista-style filters. (When you create the demo I'll call them Jack-style-filters, may I? ) Also, I understand that server-side is our job and coding the client side support is your job.

    Thank you very much for the revolution you've created with Ext.
    Jozef Sakalos, aka Saki

    Education, extensions and services for developers at new http://extjs.eu
    News: Grid Search Plugin, ExtJS 5 Complex Data Binding using MVVM


  5. #5
    Sencha User trbs's Avatar
    Join Date
    Mar 2007
    Posts
    310
    Vote Rating
    0
    trbs is on a distinguished road

      0  

    Default


    Quote Originally Posted by jsakalos View Post
    I'm glad to hear these good news from you.

    However, being the Linux guy, I have no clue what are Vista-style filters. (When you create the demo I'll call them Jack-style-filters, may I? ) Also, I understand that server-side is our job and coding the client side support is your job.

    Thank you very much for the revolution you've created with Ext.
    cool i've been wondering about that too.

    guess i'll have to wait and see what's so special about Vista *woops i mean* Jack-style-filter

  6. #6
    Ext User
    Join Date
    May 2007
    Posts
    181
    Vote Rating
    1
    akannu is on a distinguished road

      0  

    Default


    Quote Originally Posted by jack.slocum View Post
    Rich Vista-style filters can be added pretty easily with GV3. I am planning on including an example of doing it in the Ext 2.0 examples.

    Similar to the GV3 RowExpander helper class, we may implement a Filter helper class to include in the examples and handle all the plumbing for at least the client-side filtering.
    Hi Jack,
    Will this provide a row of input fields for filtering below the column headers of a grid? This is coming up a high priority for several of our apps and I'd like to know if this would be provided in EXT soon. This would help us plan better. Thanks a lot and I'd appreciate if you can provide some pointers as to when and how this may implemented.

  7. #7
    Ext User cluettr's Avatar
    Join Date
    Apr 2007
    Location
    Boston, MA
    Posts
    336
    Vote Rating
    0
    cluettr is on a distinguished road

      0  

    Default


    Saki, I like your filtering method. Are the above screen shots of an Ext client side implementation or a php project without a client side grid?

  8. #8
    Sencha User galdaka's Avatar
    Join Date
    Mar 2007
    Location
    Spain
    Posts
    1,166
    Vote Rating
    -1
    galdaka is an unknown quantity at this point

      0  

    Default


    What about this method?. Is simply, compact and more configurable.

    You can select multiple or single selection for quick and server search. View options in post.

    View thread:

    http://extjs.com/forum/showthread.php?p=32044#post32044

    View images:

    http://extjs.com/forum/attachment.ph...4&d=1179498815

    http://extjs.com/forum/attachment.ph...5&d=1179498825

  9. #9
    Sencha - Community Support Team jsakalos's Avatar
    Join Date
    Apr 2007
    Location
    Slovakia
    Posts
    27,557
    Vote Rating
    383
    jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future jsakalos has a brilliant future

      0  

    Default


    Quote Originally Posted by galdaka View Post
    What about this method?. Is simply, compact and more configurable.

    You can select multiple or single selection for quick and server search. View options in post.

    View thread:

    http://extjs.com/forum/showthread.php?p=32044#post32044

    View images:

    http://extjs.com/forum/attachment.ph...4&d=1179498815

    http://extjs.com/forum/attachment.ph...5&d=1179498825
    Hi Galdaka,

    I've implemented this a couple of weeks ago but it's very far from the ideal described above.

    Anyway, thanks for info.

    Cheers,
    Attached Images
    Jozef Sakalos, aka Saki

    Education, extensions and services for developers at new http://extjs.eu
    News: Grid Search Plugin, ExtJS 5 Complex Data Binding using MVVM


  10. #10
    Ext User
    Join Date
    Sep 2007
    Posts
    24
    Vote Rating
    0
    tboattbo is on a distinguished road

      0  

    Default


    Is there some example code out there of this filter method?