Hi all - new to these parts, but very interested in this framework - excellent job so far guys, and with the right philosophy in my opinion!
Forgive the fact that I'm not yet 100% up to speed on the internals (working on it
), but I recently spent some time at my job working on our own internal framework's query and forms APIs. We quickly discovered that not only did we simply need complex boolean logic for queries, but we also needed to take further advantage of SQL when we needed to squeeze performance using switch cases, functions etc - so we basically abstracted SQL into objects. I don't know whether you folks think going this way is overkill or not, but I think it's definitely worth keeping in consideration when designing the query API. Our object model for query abstraction was similar to the following:
// top-level interfaces and AliasableExpression abstract class
interface Query {} // represents query objects as they are being built
interface IExpression {} // represents SQL expressions
interface ISelectableExpression {} // represents those that can be used in SELECT fields
interface IGroupable {} // represents groupable properties or expressions
class Alias implements IExpression, ISelectableExpression {} // represents AS with decorator pattern
abstract class AliasableExpression implements IExpression {} // represents expressions that can be aliased with AS
/* individual sql expression types
* - SimpleValue e.g. 1, 'woot', 33.502 etc
* - Column e.g. table.column
* - Function e.g. REPLACE(arg,arg,arg,arg)
* - Evaluation e.g. AliasableExpression {OPERATOR} AliasableExpression e.g. table.column = REPLACE(x,y,z)
* - ExpressionSet with operator e.g. AliasableExpression AND AliasableExpression AND AliasableExpression
* - Cast e.g. CAST(AliasableExpression, DATETIME)
* - Case e.g. CASE WHEN ... THEN ... ELSE ... END
*/
class SimpleValue extends AliasableExpression implements ISelectableExpression {
::factory(Query $q, $value, $type);
}
class Column extends AliasableExpression implements ISelectableExpression, IGroupable {
::factory(Query $q, $columnOrAlias, $table = null);
}
class Function extends AliasableExpression {
::factory(Query $q, $functionName);
addExpression(AliasableExpression $e);; // arguments in order - arguments can be any aliasable expression, even a switch case
}
class Evaluation extends AliasableExpression {
::factory(Query $q, AliasableExpression $this, $operator, AliasableExpression $that);
}
class ExpressionSet extends AliasableExpression {
::factory(Query $q, $logical_operator);
addExpression(AliasableExpression $e);; // expressions imploded e.g. AE[0] $logical_operator AE[1] $logical_operator AE[2] $logical_operator AE[3]
}
/* alias class - can be used when selecting but not in other locations */
class Alias implements IExpression, ISelectableExpression {
::factory(IExpression $e, $alias); // $e AS $alias
}
We never got around to building the fluent query API, but we
did build a (sort-of) fluent forms API using objects similar to those above. Our challenge was that we always needed access to the $form object, and had to deal with arbitrary numbers of nested containters, so a true fluent API was too serialised for our needs. The end-result we came up with produces streams as follows:
$model = new ModelObject();
$form = new Form(new URI());
$form->build(
$form->container(
$form->container(
new FormModelBuilder(array('name', 'ipaddress', 'creationdate'), $model),
new WidgetNullStateMessage(Lang::message(CREATE_A_MACHINE)
)->template('some_form_template.tpl'),
new FormModelBuilder(array('name2', 'someotherfield', 'creationdate'), $model)
)
);
Reworking this into a SQL query API you can see how it could work out as something along the following lines. I'm not 100% sure I agree with you Kris regarding nesting being a rare case - I find that we often need to nest especially where using complex data views with user filtering. But I'm not sure whether this can be handled automatically outside of customised queries.
// in the following example, $post->query() uses func_get_args() to allow multiple arguments, where the and(), or(), like(), and gt() methods each return an expression
$filter = $post->filter();
$post->query(
$post->and(
$filter->or(
$filter->like('title', '%keyword%'), // returns a new Like object and adds it to the set
$filter->like('description', '%keyword%')
), // returns the set with two like objects and OR operator/concatenator
$filter->and( // the and is redundant here
$filter->gt('creationDate', DATE_NOW)
), // returns the set with one filter
$filter->gt('modificationDate', DATE_YESTERDAY), // works outside a set.
)
)->orderBy(
'property DESC'
); // returns the filter AND concatenator and two nested sets, and a filter applied
// outputs something along these lines
// SELECT * FROM posts WHERE ((title LIKE %keywords% OR description LIKE %keywords%) AND (creationDate > '2009-11-16 12:02:33') AND modificationDate > '2009-11-15 00:00:00'
It's not quite a 'fluent' API, but it's similar. It's effectively using a basic factory pattern on the $filter object with stuff like like(), gt(). In our framework we simply used Exp
ression() and passed in an operator, but that is a little ugly as you have to pull the operator from somewhere.
Anyhow, I thought this might come in handy! Thoughts?
Sign In
Register
Help
michelle, on 17 September 2009 - 05:17 PM, said:
MultiQuote
Why don't we merge some simple mini-sql language ( we should keep it realy simple ) and Cond::stuff(). Cond::stuff() can be very usefull in some cases, like
).