NYCPHP Meetup

[Kenneth Downs]

Allen,

This is an interesting thread you have started.  I have some suggestions
to add to the thoughts already expressed.

A very big design decision at the start is whether you will support the
validation of the "criteria" values before attempting to execute the
query.  Leaving out the validation makes it simpler to handle the overall
organization, but then the error messages given to users will be SQL
messages that they may or may not be able to figure out.  If you make it
possible to validate the expressions yourself then you can control the
error messages.

One suggestion I would make is that each filter be given a name instead of
an ID, and possibly let the user choose the name.  Then the filters behave
like functions.  This makes nesting more intuitive.  So I can put two
filters into your table:

OVER18 => 'Age >= 18'
NATIVE => 'State_where_born = State_where_lives'

Then build up complex functions through composition, so that I can have
this function:

NAT_18 => OVER18 && NATIVE

This requires your table to be set up a little differently, but will give
you complete expressive power.

A query is then really a separate thing, not stored in the same table as
the filters.  A query needs to know a table, a list of columns, and a list
of functions.  If your queries can also pull from other queries in JOIN
and UNION then you have a generalized report writer.  If you have a very
sophisticated data dictionary then your report writer can even figure out
when to JOIN and when to UNION.




> All this heirarchical structure talk happens to come up as I'm trying to
> implement a data filtering scheme for an ad-hoc querying interface.
> Basically I want to allow queries as complex as the user wants -- not
> just on one or two fields at a time -- so this has to be open-ended, and
> it seems to be pointing me to a heirarchical structure.  I think I've
> found something that will work, but I wonder a) if I'm just reinventing
> the wheel somehow, and b) if I should go with this, am I doing it right?
>
> Here goes (in English as simple as my gabby self can make it):
>
> We store all the filters using a table with these fields:
> CREATE TABLE `filters` (
>   `filterid` int(11),
>   `parentid` int(11), -- keys to filterid
>   `booltype` enum('and','or'),
>   `criteria` varchar(255),
>   PRIMARY KEY  (`filterid`)
> );
> And then we start filling it with filters. Any 'parent' filter doesn't
> store a `criteria` for itself, just the `booltype` value for all its
> children.
>
> INSERT INTO `filters` (`filterid`, `parentid`, `booltype`, `criteria`)
> VALUES (1, NULL, 'and', NULL);
> INSERT INTO `filters` (`filterid`, `parentid`, `booltype`, `criteria`)
> VALUES (2, 1, NULL, 'hair=''brown''');
> INSERT INTO `filters` (`filterid`, `parentid`, `booltype`, `criteria`)
> VALUES (3, 1, NULL, 'age=''32''');
> INSERT INTO `filters` (`filterid`, `parentid`, `booltype`, `criteria`)
> VALUES (4, 1, 'or', NULL);
> INSERT INTO `filters` (`filterid`, `parentid`, `booltype`, `criteria`)
> VALUES (5, 4, NULL, 'city=''boston''');
> INSERT INTO `filters` (`filterid`, `parentid`, `booltype`, `criteria`)
> VALUES (6, 4, NULL, 'city=''houston''');
>
> So this way I get parent filter 1 joining its children 2, 3 and 4 with
> an 'and' operator; 4 itself also is a parent of 5 and 6, which it joins
> with an 'or' operator.  The result is a filter that says:    hair =
> 'brown' and age='32' and (city='boston' or city='houston')
>
> This seems open-ended enough to be very flexible, and it makes sense to
> my feeble brain, but is there a better way to do it?
>
> - Allen
>
> --
> Allen Shaw
> Polymer (http://polymerdb.org)
>
> _______________________________________________
> New York PHP Talk Mailing List
> AMP Technology
> Supporting Apache, MySQL and PHP
> http://lists.nyphp.org/mailman/listinfo/talk
> http://www.nyphp.org
>


-- 
Kenneth Downs
Secure Data Software
631-379-0010
ken at secdat.com
PO Box 708
East Setauket, NY 11733