NYCPHP Meetup

[Allen Shaw]

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)