GORM Goodness in PHP
January 10, 2008
I’m working on a way to port the ‘magic’ goodness of GORM to PHP. I’ve thought about it for awhile, but haven’t had much of a need to. I’ve got a need for it now, so I’ve whipped up a basic prototype. This will allow me to do things like:
$user->findAllByFirstname(”Mike”);
and automatically get an SQL statement like
“select * from user where firstname = ‘Mike’
If I get this working transparently enough, I may donate it to some framework project (Zend, Symfony, etc.)
Any thoughts on this from the community?
Did you like this post? Buy me a hot chocolate!
Posted in Development, MySQL, PHP, Web, opensource
January 10th, 2008 at 9:41 am
Hi Michael!
You might want to check out Doctrine:
http://www.phpdoctrine.org/
January 10th, 2008 at 9:47 am
I’d consider looking at Doctrine - http://www.phpdoctrine.org - since they’re doing the best ORM for PHP and would probably cover all you need.
January 10th, 2008 at 10:06 am
Interesting. The following page:
http://www.phpdoctrine.org/documentation/manual?chapter=dql-doctrine-query-language
briefly describes a ‘magic finder’ (at the very bottom), which is what I was talking about. Doesn’t look like it handles compound queries ($user->findAllByFirstnameAndLastname(’mike’,'kimsal’);) but it’s a start. Can Doctrine handle that?
January 10th, 2008 at 10:08 am
BTW, thanks for the pointer to doctrine.
January 10th, 2008 at 10:31 am
Hrm. So it looks like Cake has this as well, but again with the ‘one field’ limit:
http://manual.cakephp.org/chapter/models
in the middie of the page there’s a bit on finding.
$this->Author->findByLastName(’Rogers’);
I think people aren’t doing the OR/AND options because they don’t restrict each search property name from being camelCased. No easy divider (unless you went with _)
January 10th, 2008 at 10:32 am
CakePHP (www.cakephp.org) has this functionality too. You can achieve the same thing with a custom __call() magic method. Something like (untested and typed straight in the comment box):
function __call($name, $var) {
if (substr($name, 0,9) == “findAllBy”) {
sql = “SELECT * FROM table WHERE “.substr($name, 9).” = ‘”.$var.”‘”;
}
}
January 10th, 2008 at 10:34 am
(oops - missed mgkimsal’s comment) >.
January 10th, 2008 at 10:49 am
I guess my approach was to do more parsing on the magic find() method. find vs findAll (one row vs all rows), for example. And again, something like
$user->findAllByCityAndState(’raleigh’,'nc’)
is much more useful than only doing the single key/val search.
January 10th, 2008 at 11:54 am
Solar_Sql_Model::__call() does exactly what you’re asking for. E.g., fetchAllByCityAndState(’raleigh’, ‘nc’) generates a “WHERE city = ‘raleigh’ AND state = ‘nc’”.
http://solarphp.com/class/Solar_Sql_Model::__call()
January 10th, 2008 at 11:55 am
Hm, you link-generator-parser drops the () from the link above. Here’s the full class documentation:
http://solarphp.com/class/Solar_Sql_Model
January 10th, 2008 at 11:58 am
$this->User->findAll(’city = ‘Miami’ and state = ‘FL’) seems reasonable to be… but that’s just me.
January 10th, 2008 at 12:23 pm
Thanks everyone for the feedback. Glad to hear this is in SOLAR.
@Jeraimee - I believe that GORM allows for the use of arrays as well as parameters, so that if an argument was an array, it would automatically create the appropriate in’ syntax for SQL.
January 10th, 2008 at 2:07 pm
The Zend framework already has this built in.
http://framework.zend.com/manual/en/zend.db.table.relationships.html
January 10th, 2008 at 2:54 pm
Hey JayBill:
Thanks for pointing that out. Recalling back, I did read about that a few months back, but it didn’t seem to be the same thing. I’m only seeing a ‘getBugsByEngineer()’ example, and it seems I have to do some other specification in the definitions to get that. Am I wrong? I certainly might be. What I’m reading is that you get $row->find
By()
but I have to define the Rule first.
Again, am I missing something? The cake and doctrine options seem closer to what I’m talking about.
Thanks.
January 10th, 2008 at 2:55 pm
Sorry- that was $row->find_TableClass_By_Rule_(). It was original left and right than braces, but they didn’t show up!
January 10th, 2008 at 11:13 pm
[...] Michael Kimsal’s weblog » GORM Goodness in PHP If offered some advice on how to implement a findAllBy… magic method using __call() (tags: php magic_method) Posted by Richard@Home Filed in 15 [...]
January 12th, 2008 at 3:23 am
Michael, you might want to have a look at Limb3 ActiveRecord package. It’s pretty much Rails ActiveRecord alike and has proved to be stable and quite useful in many our applications.
Sorry, there’s no English documentation(only Russian atm) but you may want to have a look at tests in the source - https://svn.limb-project.com/limb/3.x/trunk/limb/active_record/tests
March 9th, 2008 at 11:59 am
I would like to point out that Doctrine HAS magic methods but they do NOT RECOMMEND USING THEM! The magic methods pre-load the whole table and whittle it down to your desired results; unless your table is a tiny propertyset table, this is a lot of overhead.
Doctrine has its own convention for search result formation which is very powerful and flexible once you get used to it:
$matches = Doctrine_Query::create()
->from(’Users’)
->where(’name LIKE ?’)
->execute(array(’Mike’));
$matches now contains an array of records matching the criteria of the query it generated (from a factory method). This syntax works because each of the modifying methods of the Doctrine_Query object returns itself as a result, except for the last execute() method, which returns data.
Those looking to adopt Doctrine are well advised to study its hydration and memcache implementation systems. Both have a major impact on Doctrines’ utility.
March 9th, 2008 at 12:13 pm
Wow - that’s odd that doctrine doesn’t just parse the magic method name and create some SQL for you. Are you sure it doesn’t do that? That seems that it would be the most logical approach by far.
Looking at the http://www.phpdoctrine.org/documentation/manual?one-page docs again I’m not seeing any magic method approach at all. The closest I see is a ‘findByName’ but it’s a reference to a hardcoded method.
April 1st, 2008 at 12:01 am
If you think about it, the number of scenarios when querying for data from a dbms are too great to be covered by some magic findBy methods
The magic find by methods could and most likely will be enhanced to include “And”, “Or” keywords when building the DQL query, but those are just convenience shortcuts to generating single table DQL queries. DQL exists for querying for the data efficiently no matter how complex the relationships or number of models involved.