23 May 2007 5:38 AM #1
An Ideal Grid Filter for Large Databases
An Ideal Grid Filter for Large Databases
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?
23 May 2007 7:07 AM #2
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
Sencha Co-Founder, Ext JS Founder
Original author of Ext JS 1, 2 & 3.
23 May 2007 8:17 AM #3
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.
23 May 2007 8:40 AM #4
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.
23 May 2007 4:52 PM #5
7 Aug 2007 10:55 AM #6
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 Aug 2007 6:34 PM #7
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 Aug 2007 2:01 AM #8
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.
16 Aug 2007 4:16 PM #9
20 Oct 2007 6:38 AM #10
Is there some example code out there of this filter method?