Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

Introducing FilterOnClick for Perl

by trs80 (Priest)
on Oct 15, 2005 at 17:53 UTC ( #500472=perlmeditation: print w/replies, xml ) Need Help??


In 2002 I posted a node entitled RFC on module idea, well nearly 4 years later I have a 1.0 version release of the concepts first discussed in that post. Introducing FilterOnClick. All the features mentioned in my original post either exist or have been improved upon under the FilterOnClick banner.

The current implementation is a Plugin for Class::DBI and is available as Class::DBI::Plugin::FilterOnClick. FilterOnClick from an abstraction standpoint owes a great deal to the Class::DBI approach to database interaction.

FilterOnClick is based on two specific techniques of interacting with data sets:

Knowledge based interaction - Interaction when you know what you are looking for. Example - You know the person wants all the items in blue with a star pattern.

Discovery based interaction - Not knowing specifically what you are looking for or perhaps looking for an anomaly. Example - You want to look for out of place data or interesting values to determine their frequency of occurrence.

The name FilterOnClick is derived from the actual interaction with the data sets via the browser. An HTML table is produced and particular values within a cell can be clicked on (if configured appropriately) to "filter" the results. If a column (database field) is configured as searchable a search can be done based on any string. It also has support for NULL and empty fields to assist with finding an anomaly. Another feature is the ability to cascade filters across multiple columns. This allows for a drill down into a specific type of item you would like. In addition to cascading they are not fixed in order so you can selectively remove or add filters at anytime in the process.

An example use of FilterOnClick would be an automobile sales web site.

We will use a database containing car information as our example dataset. In the database you have the common attributes of each vehicle; mileage, color, price, transmission type, year, make, model, etc. Using Class::DBI::Plugin::FilterOnClick you can create a CGI script and make any click on year equal a filter for an exact match look up if clicked. That would allow a user to find the year they are interested in and then filter to just that year. A variance filter offers a little power, you can use the variance and look up and down x number of years, where x is the difference.

So using a variance of 2 instead of an exact match allow the user to show all cars between 2001 - 2005 years if they clicked on 2003.

If price is added and setup as searchable + a variance you could search for all cars with an exact price (say $20,000, for example) and then with a percent variance click on any $20,000 cell value and find all cars between 2001 - 2005 priced within 5% of $20,000 since our filters cascade.

Now we add another FilterOnClick rule on mileage, here again we use a variance, this time 10% and that column is searchable as well. We type in 30000 for the search criteria which shows all vehicles with 30K (if any), we can now click on a 30K value in any cell in the price column.

With all the above filters available and applied the user will now see all 2001 - 2005 cars with 27,000 - 33,000 miles on them with a price between $19,000 and 21,000.

Now based on our available options our user decides the year restriction is too limiting, which is what we chose first, we can now click on any value in the year colum and the filter on year is remove, but the other filters remain valid. In other words we are only filtering on price and miles now.

Multiple combinations of the above can be created simply by adding a filter type on a particular column. FilterOnClick makes it easy to create unique lookups that otherwise would require custom programming to meet a particular condition that is prone to change.

Class::DBI::Plugin::FilterOnClick can also be used to connect databases that you don't have write access to. Using a connector table you can record notes based on the your discovery in the read only database back to your writable database. You can even include content from within multiple databases with a single table based on the primary key of the base table.

Feature List
  • Percentage Variance based filters
  • Numerical Variance based filters
  • Exact match based filters
  • Starts with match based filters
  • Ends with match based filters
  • Order by on a per column basis (currently only supported on the base table)
  • Ability to color(ize) values matching a regex on a per cell basis
  • Substitute your own custom filter by passing in a subroutine
  • Automatic record set navigation creation
  • Configuration file based settings, currently based on Config::Magic allowing for multiple configuration file formats
  • Searchable fields based, including the ability to find NULL and empty strings
  • CSS based coloring of table elements
  • Creation of string based navigation (alphabetical, numerical, groups of either, etc.)
  • Highlighting on currently filtered columns
  • Ability to add custom columns and include data from alternate datasources (excel, access, text files, etc)
  • Tables based on HTML::Table allowing for OO based access to auto rendered table for further post generation tweaking
  • Result pages can be booked for future reference or mailed to other users (assuming you use GET and not POST for the method on the form)

Future Features

  • Less than/equal Variance based filters
  • Greater than/equal Variance based filters
  • Order by based on columns from any table (most likely to completed by a secondary JavaScript level sort)

FilterOnClick Demo site

This module relies heavily on a number of other CPAN modules so thank you to all the contributors to CPAN for helping make my module possible.

Replies are listed 'Best First'.
Re: Introducing FilterOnClick for Perl
by CountZero (Bishop) on Oct 15, 2005 at 19:28 UTC
    This module is a wonderful thing. I must have seen one of your previous efforts (and promptly forgotten about it!) because about half a year ago I "invented" a similar "filter-on-click" interface for a client's web-based interface to his database.

    If you allow me, I have a few questions and comments:

    • I have found it useful to add a "clear all filters"-button to each page, just for the case someone selects a combination of filters which yield an empty result set. It seems some users don't know what to do then and don't find the obvious answer "hit the back button". Also it saves some clicks and return trips to the database to start a new search afresh.
    • Using HTML::Table is a good idea. I did it myself too. Recently however, I had the need to have access to the individual rows (or even cells) of the table, mainly because the page was written with the Template Toolkit (as the View component of Catalyst). A switch which would allow the output of your module to be either in the form of an HTML::Table or in the form of a two-dimensional array, would be really useful in that respect.
    • Finally a choice about the type of config-file would be useful too, I rather like to put things in YAML-format and it would be really nice to put all configuration data in one single file, rather than have multiple files each define parts of the whole application.


    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

Re: Introducing FilterOnClick for Perl
by bageler (Hermit) on Oct 16, 2005 at 21:37 UTC
    I _just_ implemented a similar functionality to an AJAX based website for log viewing. I send the data to the client then do sorting (no filtering yet, but the OP has me inspired :) ) based on the data stored in local browser memory, rather than having to re-query the servers each time. Not very perlish, but here's what I did in case anyone is interested.
Re: Introducing FilterOnClick for Perl
by zby (Vicar) on Oct 17, 2005 at 12:53 UTC
    ++ great work. I want just to add that you can consider using Class::DBI::Sweet instead of the dated Class::DBI::Pager module (it would replace some other of the used modules as well). Class::DBI::Pager does not use the 'LIMIT' clauses in the database queries so can lead to performance issues. There is also Class::DBI::Plugin::Pager - which does use 'LIMIT' but Sweet is much sweeter.

    You might also have a look at the Ajax sortable tables: RFC : AJAX + DBI = DBIx::LiveGrid and think about integrating your modules.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlmeditation [id://500472]
Front-paged by ghenry
[ambrus]: ething that's both cleanly organized and mostly automated.
LanX in train, bad connection
[Corion]: ambrus: Yeah - we're in that situation too, except that there is no time to do the reorganizing :-/
[LanX]: ... so my boss started a project with the newest sun servers and invited the traders to come on weekend to test it... and they were so pleased, that they forced him to keep it in production...
[ambrus]: Corion: sure, this is the long-term plan. The short term is that I have to run this ungodly mess to get results from the new input data today.
[Corion]: ambrus: Most of our "automation" is tied to process exit codes and a shell pipeline :-\
[LanX]: ... a week later they realized that one of the databases - which recorded how much the other banks due to this bank - was not correctly plugged
[ambrus]: Corion: I have no problem with exit codes and shell pipeline. My problem is that the current process requires a lot of manual intervention from me, including editing the source codes.
[ambrus]: (Also a lot of manual intervention by two or three other co-workers, who do other parts of the process.)
[ambrus]: Some of the manual part is unavoidable, but not all.

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (16)
As of 2017-03-29 11:49 GMT
Find Nodes?
    Voting Booth?
    Should Pluto Get Its Planethood Back?

    Results (350 votes). Check out past polls.