Page 1 of 1

SQL query modification via strategy pattern?

Posted: Mon Jun 11, 2007 8:59 am
by Chris Corbyn
I'm writing some code which acts as a search component, returning primary keys for later use inside our application. Currently all "filters" on are hard-coded into the search class. These are effectively just bare comparisions against field values:

Pseudo:

Code: Select all

$search = new SearchCriteria();
$search->setFieldOne('some value');
$search->setAnotherField('some other value');

var_dump($search->getPks()); //Primary keys
This works beautifully except it's now growing beyond a maintainable size and we also want to create a view helper using this as a model. I've taken the decision to refector the search object into a central class, with loosely coupled strategies attached.

Code: Select all

$search = new SearchComponent();
$search->addStrategy(new DateStrategy('some date'));
For the "View" components there's another layer sitting on top, but this is irrelevant right now.

The problem I'm facing right now is that each strategy needs to be able to access a SQL query which is being manufactured inside the central component. The strategy should then be able to add an tables it needs to a SELECT query, along with any search conditions in the WHERE clause. Passing a string is going to have me jumping through hoops so I was hoping to pass around a nice "Query Builder" object with some ORM type of methods like

Code: Select all

$q->addTable('table1');
$q->addTable('table2');
$q->addCriteria('table1.somefield', 'some value', Builder::EQUAL);

echo $q->getSql();
Does anyone know of something like this which is up to date? So far I've found only one class on PHPClasses.org but I can't stand the site and I've never had a great deal of success getting good code from there. I have started writing my own but don't really want to re-invent the wheel :)

Posted: Mon Jun 11, 2007 9:24 am
by Jenk
I started making one of these a while back.. it becomes apparent early on just how phenomenally huge the task is for covering all possibilities.. you may as well just use straight SQL statements.

Posted: Mon Jun 11, 2007 9:32 am
by Chris Corbyn
I'm happy to start basic with something that meets our requirements and then build on top of that as needed.

Right now all we need is:

SELECT <fields/aliases>
FROM <tables/aliases>
WHERE <criteria>

Within the FROM part we'd need left joins to be available, and within the WHERE clause we just need basic operators, AND/OR switches. It's literally this simple right now. Running "HELP SELECT" in MySQL does reveal how phenominally huge this job could become if it had to be FULL featured though. We can extend as time goes on. If nobody has any leads on other classes I'll just carry on with my basic version for now... I'm amazed nobody else has done this before.

I can see how it would seem pointless on first sight. If someone had posted a "query builder" yesterday I would have thought it was a bit of an overkill, but when you need to have several objects modifying one query it starts to make sense :)

Posted: Mon Jun 11, 2007 10:13 am
by kyberfabrikken
I have one such class, which I use when I have to create WHERE clauses dynamically (Eg. a search query). It's based on PDO. If you want it, I can post it for you.

Posted: Tue Jun 12, 2007 12:39 am
by Chris Corbyn
kyberfabrikken wrote:I have one such class, which I use when I have to create WHERE clauses dynamically (Eg. a search query). It's based on PDO. If you want it, I can post it for you.
Thank you. I would be greatful if I could have a look at it yes thanks :)

Posted: Tue Jun 12, 2007 2:54 am
by kyberfabrikken
d11wtq wrote:Thank you. I would be greatful if I could have a look at it yes thanks :)
I can't post attachments on this board, so here's a link:
http://code.google.com/p/pdoext/

I don't have any tests for it. I had some at one point, but they didn't get maintained, so they aren't worth anything now. I'm fairly confident that the code is stable though, since I use it in several applications.

Just in case the code isn't self-explanatory, here are some usage examples:

Code: Select all

$db = new PdoExt(...);
$q = new pdoext_Query($db, 'people');
$q->addCriterion('first_name', "John");
$q->toSql(); // select * from `people` where `first_name` = 'John'

$db = new PdoExt(...);
$q = new pdoext_Query($db, 'people');
$q->addCriterion('first_name', Array("John", "jim"));
$q->toSql(); // select * from `people` where `first_name` in ('John', 'Jim')

$db = new PdoExt(...);
$q = new pdoext_Query($db, 'people');
$q->addCriterion('first_name', NULL, "!=");
$q->toSql(); // select * from `people` where `first_name` is not null

$db = new PdoExt(...);
$q = new pdoext_Query($db, 'people');
$j = $q->addJoin('accounts', 'LEFT JOIN');
$j->addConstraint('people.account_id', 'accounts.account_id');
$q->toSql(); // select * from `people` left join accounts on `people`.`account_id` = `accounts`.`account_id`

$db = new PdoExt(...);
$q = new pdoext_Query($db, 'people');
$q->addColumn("first_name");
$q->addCriterion('first_name', "John");
$q->toSql(); // select `first_name` from `people` where `first_name` = 'John'

$db = new PdoExt(...);
$q = new pdoext_Query($db, 'people');
$q->addColumn("first_name as name", FALSE);
$q->addCriterion('first_name', "John");
$q->toSql(); // select first_name as name from `people` where `first_name` = 'John'

$db = new PdoExt(...);
$q = new pdoext_Query($db, 'people');
$sub = $q->addCriterion(new pdoext_query_Criteria("OR"));
$sub->addCriterion('first_name', "John");
$sub->addCriterion('first_name', "jim");
$q->toSql(); // select * from `people` where (`first_name` = 'John' OR `first_name` = 'Jim')

Posted: Tue Jun 12, 2007 6:40 am
by Chris Corbyn
Awesome thanks :) I probably won't get a chance to play with it much until tomorrow since I've got a milestone to complete by the end if today. No worries about the tests too, I trust your work.

Posted: Fri Jun 15, 2007 6:46 am
by Jenk
Just to add a bit of necro..

I've recently been working with the ORM framework for Smalltalk called GLORP. This thing is utterly, utterly fantastic. However, it is nigh on 200 classes. TestCases alone 100+, too.

I've been examining the section relevant to the query building, and that in itself is 60+ classes, with about 35 testcases. It uses descriptor classes to provide rules for storing the data, so that it then beaks down the objects gracefully for storing in tables.

Anywho.. just to give an idea of just how large a project like this can grow..

Posted: Tue Jul 17, 2007 5:12 pm
by kyberfabrikken
FYI, I decided to put the code up at http://code.google.com/p/pdoext/

Posted: Wed Jul 18, 2007 7:58 am
by Chris Corbyn
Sorry, I forgot to back to you about this :) Thanks for releasing the code. In the end, what I've ended up doing is (and I'm still working on it) extending a load of ORM stuff to handle what we need because we opted to write our own ORM system anyway :)

Posted: Wed Jul 18, 2007 3:06 pm
by kyberfabrikken
d11wtq wrote:Sorry, I forgot to back to you about this :) Thanks for releasing the code. In the end, what I've ended up doing is (and I'm still working on it) extending a load of ORM stuff to handle what we need because we opted to write our own ORM system anyway :)
No reason for being sorry -- I've had a few inquiries about the code, and I have used it in the odd project here and there, so I figured I might just throw it out in the open.

Just curious; If you're going the ORM path, why didn't you pick an existing package?

Posted: Wed Jul 18, 2007 5:09 pm
by Chris Corbyn
kyberfabrikken wrote:
d11wtq wrote:Sorry, I forgot to back to you about this :) Thanks for releasing the code. In the end, what I've ended up doing is (and I'm still working on it) extending a load of ORM stuff to handle what we need because we opted to write our own ORM system anyway :)
No reason for being sorry -- I've had a few inquiries about the code, and I have used it in the odd project here and there, so I figured I might just throw it out in the open.

Just curious; If you're going the ORM path, why didn't you pick an existing package?
I wanted to use Propel myself. We're in the process of building (rewriting a dinosaur) a framework and my boss doesn't much like the compleX(ml) configuration/number of classes etc involved in ORM. I've gone down a purely PHP route using a little reflection and giving the appearance that "orm is simple" to my boss ;)

Creating a new mapping is just a case of writing a class with class constants for each field and the table name. There are some extra "optional" properties than can be set but they're not needed by default. Reflection and overloading provides most of the remaining interface.

Either way I've already ended up with Orm, OrmJoin, OrmCriteria, OrmTypes and OrmSqlOptimizer classes, but that's pretty transparent when it comes to usage/configuration :)

Basically it's purely an interface thing.

EDIT | I have weighed up the big drawback to this approach too.... Documentation generation with Doxygen/PHPDoc is limited when it's all reflected/overloaded.