NYCPHP Meetup

[Allen Shaw]

Hi All,

Wow, I see a lot of discussion has happened since I read my mail last 
night.  (Shame on me for starting a thread at 6 PM on a Friday, anyway...)

Without backing up too much, I guess I should clarify a little bit more. 
  What I'm looking for here is a way to store and retrieve an unlimited 
combination of boolean filters against a flat table of information.  To 
get a little more real-world, I should tell you the idea is that this 
interface will offer users a choice of several pre-defined views (which 
are created using whatever/however in a backend that's not known), so 
the user picks one of those and effectively has access to one flattened 
summary set of data.  So we're not going to have this user making joins 
or creating table relationships or whatever, just looking at a flat 
view, and allowing them to filter it if they want.

I want to make sure the user can filter that data in as simple or 
complex a manner as he or she needs.  A system like this needs to be 
able to store criteria in boolean relationships with precedence 
grouping, so it could record structures like "(criteria_A and 
criteria_B) or (criteria_C and criteria_D)", etc.  As long as we can 
track simple boolean operators (basically and|or) and record correct 
precedence groupings for combinations of those operators (basically, 
putting the parentheses in the right places), then we can create filters 
of unlimitted complexity.

You can see this is a kind of generic discussion. By that I mean that 
the format or content of "criteria_A" or "criteria_B" would depend 
heavily on the backend system, whether it's a particular database server 
product or some custom parser that applies criteria agains flat-file 
data or XML files or whatever.  (For example, if we're talking PHP/SQL 
here, the filter described above would amount to something like "where 
($criteria_A and $criteria_B) or ($criteria_C and $criteria_D)".)

The application of course has to figure out whether the criteria are 
valid, help the user enter valid criteria, etc., and it also has to be 
responsible for creating the views in the first place, and applying that 
filter, etc.  But I think the actual process of tracking these nested 
filter criteria will 1) be a good context for discussion of heirarchical 
data structures, and 2) result in a reusable structure that could be 
applied in any filtering system, regardless of the data backend or type 
of data.

Kenneth Downs wrote:
>> 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:

This is not such a bad idea, but it requires me (or whatever admin) to 
create the filters ahead of time and offer them to the user.  Hopefully 
I can come up with a structure that allows for recording whatever 
filters the user can come up with based on the existing columns in the 
flat data view.

Sorry if I didn't explain myself well in the beginning. I think right 
now it's really just about defining the best structure for storing 
filter criteria and the criteria's relationships to each other.  If we 
can do that we have a method that could be useful in numerous different 
systems.  Isn't that right?

- Allen


Kenneth Downs wrote:
>>On 07:56 AM Kenneth Downs wrote:
>>
>>>It is not so much implementing views as giving users a way to specify
>>>views.  Since a view is just a stored query, it is just as well to
>>>generate the query at run-time if doing so gives more flexibility to
>>>the user.
>>
>> > A view on the other hand must be defined by an administrator
>>
>>>and so is outside normal user activity.
>>
>>Creating views is not outside user activity:
>>Create_view_priv has been available since MySQL 5.0.1.
> 
> 
> Perhaps I should phrase it differently.  When a user is specifying search
> criteria, they are not interested in whether they are creating some
> server-side object, they just want the result.  So while it may or may not
> be a programmer's decision to implement the result as a view, the design
> decisions in the original post should focus more on how to lay out tables
> that will allow the user to specify criteria.
> 
> Also, there are other databases in the world besides MySQL.
> 


-- 
Allen Shaw
Polymer (http://polymerdb.org)