justspree — 2012-07-10T00:05:58-04:00 — #1
Hi, would like to know what kind of design patterns is suitable for dynamic generate SQL? Like for example I have a class solely for one database table:
Select * from user WHERE userid = "' . $userid . '"
Then someday later another programmer decided to extend the feature and add another user_log table. He needs to add in criteria for the sql like so:
Select * from user u LEFT JOIN user_log ul (ON ul.userid = u.userid)
WHERE u.userid = "' . $userid . '"
What kind of methods/patterns do you guys adopt?
Thanks in advanced
cups — 2012-07-10T11:07:34-04:00 — #2
I guess this is taken from a User object and you can structure and name things and generally set up "scaffolding" which handles the 1:1 table transactions, that bit is quite easy - take a look at the ActiveRecord pattern
The fly then lands in the ointment when you want to do joins - try this DataMapper discussion for a taster.
Ostensibly you'll may find yourself heading in the direction of ORMs which handle and abstract away the joins - read [this post and [URL="http://www.sitepoint.com/forums/showthread.php?655080-DataMapper-vs-ActiveRecord&highlight=objects+joins"]this one](http://www.sitepoint.com/forums/showthread.php?722015-Do-you-use-an-ORM-outside-a-popular-framework&highlight=objects+joins).
There are many schools of thought, one is that if you don't envisage many joins in the future, then just live with it and tunnel some way through to permit direct access or injection of an sql string as an argument - not very pure in OOP terms - but will get the job done.
I think a lot depends on whether you are in fact making yourself an "SQL Query Generator" which just joins up strings or by extending something like PDO and using its prepared statements.
If you want to know more or read more real-life experiences of OOP/DB issues do an Advanced Search on just this forum for the terms:
Another thing to know. This kind of data persistence issue is generally handled by the layer termed the "Model" (the M in MVC), the model can be doing other things but it is generally where you keep the code that writes to the DB.
justspree — 2012-07-11T00:42:51-04:00 — #3
Thanks for pointing out directions Cups! I'll read up on those!
tomb — 2012-07-11T04:23:25-04:00 — #4
In addition to the topics Cups posted, there was a good discussion here: http://www.sitepoint.com/forums/showthread.php?687271-New-PHP-Data-Mapper-Library about specific implementation considerations when trying to build a DataMapper