I'm curious on what you could come up with. =)
The issue I run into often with regards to a helper to generate more-custom WHERE clauses includes things like:
* Dealing with NULL
* allowing for <, >, <=, >= and !=
* accomodating the use of LIKE and %
* accomodation the use of FULLTEXT (forgot the syntax)
* Dealing with AND/OR, and nested ANDs/ORs
But i kinda see where you're going with this. If you could design out a way that on a generated object, you could pass in just some parameters you want to do a search on, i think it'd be a useful candidate to add to the core templates. So that for any given object, you could do (pardon the pseudocode)
Foo::LoadBySearching(
(“FirstName LIKE 'John%'”) OR (“FirstName LIKE 'Chris%'”) AND
(“Age <= 15”) AND
(“ActiveFlag = 1”)
To find all Foo's who's first name starts with John or Chris, and who is 15 years old or younger, and who's record is Active.
But the key would be to have this method with a straight-forward enough protocol so that it's simple to use, but at the same time feature rich enough to allow for some real usefulness.
On a slightly different note, someone (forgot who it was) made a suggestion once that maybe just to allow a developer to pass in an arbitrary WHERE clause to the Load statmeent, so something like:
Foo::LoadWithWhere(“((first_name LIKE 'John%') OR (last_name LIKE 'Chris%')) AND age <= 15 AND active_flag = 1”);
I'm hesitent to go with this approach because:
1. I feel like it's a security hole just asking to be hit with SQL injenction =)
2. It starts to allow for some sloppier object-oriented design principals
3. I understand that being able to write your own where clauses is very important. I do it all the time on my projects. =) So if you need to write custom SQL, why not just write your own load handler, so that it's a much cleaner approach?
class Foo extends FooGen {
public static LoadByQualifiedApplicants() {
$objResult = Application::Database[1]->Query('SELECT * FROM foo WHERE ...');
/* OK, some people don't like SELECT * -- of course, you can explicitly name columns here, too =) */
return Foo::InstantiateDbResult($objResult);
}
}
This way, the specific logic you are coding to do this specific special where clause is clearly deliniated as a separate business rule (with it's own method).