Generic Declarative Expression Builder in PHP

Question

Folks,

I'm looking to build a piece of PHP5 UI that I'm pretty sure is common to a bunch of applications. Basically, it's an expression builder that allows users to specify expressions combined through logical operators (AND/OR), like this:

• FieldX > 3 AND FieldY = 5
• FieldY = "bob" and FieldZ is not null
• FieldX > '5/23/2007' OR (FieldY = 5 AND FieldY is not null)

Ideally, a system like that would allow me as a programmer to specify the list of parameters that the user can pick (columns) and the data type for each. It would also provide a nice, user-friendly interface for defining expressions like that - I'm imagining something like a table, where each row has several pickers:

[Column] [Condition] [Value] [AND/OR] [Column] [Condition] [Value] [AND/OR] ...

Do you know of an open-source component that does something like that? Or maybe a part of this kind of functionality?

Bloodred

Answer 1

A word of caution. These types of generic expression builders are commonly thought of as excellent solutions to a variety of user problems. The thinking tends to go along these lines:

• The user wants all these different reports
• There's no way we're giving them SQL access (even if they could understand it)
• We don't have time to write all these custom reports
• Therefore, I'll make an easy way for them to write queries without having to know specific syntax, tables and fields.

This is not necessarily a bad idea, but what we have found at my company is that non-techie users have surprising difficulty understanding and building expressions more complex than a group of ANDS or a group of ORS. This is hard for us programmers to grasp, as most of us probably had an intuitive understanding of boolean logic even before we learned to program.

I would recommend that you not try to give them complete flexibility to create whatever expression they want, and instead you put together a UI that lets you the programmer define something more complicated on the back-end but gives the user simple choices on the front-end. This is easier said than done, obviously.

Remember - sometimes the difficulty for end users isn't the fact that they don't know the right syntax to express their idea in. More often it's because they don't even know how to formulate their thoughts in an unambiguous way, even if they were provided an easy way to do so.

NOTE: I'm not saying the end users are always morons - just that their minds may work differently from us crazy developers.

Facing

This is a great, great point. Most people do not think in terms of decision trees. – Grip

Answer 2

I think this is a very interesting idea. Are you planning on working on this project for personal use or through work?

If you are planning on working on this project personally, maybe you should write it yourself and turn it into an open source project. It looks like there is already some interest in this area and it would be a good learning experience.

Otherwise, I can see how a company could find value in something like this. It would give the programmers another utility that could help them do their job a little bit easier and pay dividends in the long run.

Either way, this project is win. You will learn alot, and create something that other people find useful.

Cony

Answer 3

This isn't exactly a component, but you might take a look at the Conditions tab of the Shopping Cart rule builder in Magento for a solid PHP implementation of this type of functionality. It is customized towards e-commerce, so it is not a generic database overlay, but it does have a great condition builder interface.

Grip

What version is this in Steve? I think I am using 1.1.3 and it doesn't have this... – Caphaitien

It should - in the main horizontal menu, go to Promotions > Shopping Cart Price Rules. Then add a new rule. In the new rule form, click the tab on the left labeled 'Conditions'. I'm running 1.0.19+ – Grip

Answer 4

Check this out: http://www.codeproject.com/KB/custom-controls/SqlWhereBuilder.aspx

Download the client side JavaScript Library version of the component and customize it to your needs. Cheers.

Wonderful

Answer 5

I'd start by creating an object oriented query model. Eg. Criteria-objects. The next step is then to write a UI which allows manipulation of this model structure.

Daisie

Answer 6

I've recently done such functionality by myself and, IMHO, it's easier to write your own implementation.

Salome

Yes, PHP. Actually it was lame and straightforward. Here is rule structure: $rule = array( 'field' => 'name', 'action' => 'eq', // test for equality 'value' = 'John*', 'bit_operation' => 'AND' // join between this condition and next } Rules was saved to array in session and used by SQL query building function. No classes, no OOP - just this :) – Salome

Answer 7

I think this is more related to UI than PHP in general. You'd better retag your question. Maybe you just want to parse these definitions in PHP, in this case I'd suggest using preg_replace_callback.

Vaniavanilla

Answer 8

why dont try using smarty which is a template engine that. you just need to get and parse the user input.

Now that I'm thinking, do this in any way will need you checking the user input to avoid injections.

Massasauga