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
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.
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:
DataMapper
ActiveRecord
ORM
for starters.
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.
