Zend_Db_Table dynamic finders

Posted on June 15, 2009 by ekeretex

The recently added Zend_Navigation component uses dynamic finders to find pages e.g. findOneByLabel(‘Home’) to return the first matching page with label Home (and that’s straight from the manual).
It would be nice if Zend_Db_Table could do this too but it can’t. This seems a little inconsistent to me. Why do it for some components and not others?

Anyway, adding it wasn’t that difficult. The first step is to have an (abstract?) class extend Zend_DB_Table and let all your Db-backed models extend that. I’ll call mine App_Db_Table.

001<?php
002 
003abstract class App_Db_Table extends Zend_Db_Table_Abstract {
004 
005    /**
006     * Db Instance
007     */
008    protected $_db;
009 
010    /**
011     * Call method used to implement the dynamic finders
012     *
013     * @param string
014     * @param array
015     * @return function || void
016     */
017    public function __call($method, $args) {
018        $watch = array('findBy','findAllBy');
019        foreach ($watch as $found) {
020            if (stristr($method, $found)) {
021                $fields = str_replace($found, '', $method);
022                return $this->{'_' . $found}($fields, $args);
023            }
024        }
025        throw new Exception("Call to undefined method App_Db_Table::{$method}()");
026    }
027 
028    /**
029     * Initialize object
030     *
031     * Called from {@link __construct()} as final step of object instantiation.
032     */
033    public function init() {
034        $this->_db = Zend_Registry::get('db');
035    }
036 
037    /**
038     * Find By
039     *
040     * This method only ever returns the first record it finds!
041     *
042     * @param   string
043     * @param   array
044     * @return  object|false
045     */
046    protected function _findBy($columns, $args) {
047        $stmt = $this->_buildQuery($columns, $args);
048        return $this->fetchRow($stmt);
049    }
050 
051    /**
052     * Find All By
053     *
054     * @param   string
055     * @param   array
056     * @return   object|false
057     */
058    protected function _findAllBy($columns, $args) {
059        $stmt = $this->_buildQuery($columns, $args);
060        return $this->fetchAll($stmt);
061    }
062 
063    /**
064     * Builds the query for the findBy methods
065     *
066     * @param   string
067     * @param   array
068     * @return  Zend_Db_Select
069     */
070    protected function _buildQuery($columns, $args) {
071        $fields = explode('And', $columns);
072        $count = count($fields);
073 
074        $where = "{$this->_filterField($fields[0])} = ?";
075        $where_args = $args[0];
076        unset($args[0]);
077 
078        $select = $this->select();
079        $select->where($where, $where_args);
080 
081        if ($count > 1) {
082            array_shift($fields);
083            foreach ($fields as $field) {
084                $where = "{$this->_filterField($field)} = ?";
085                $where_args = array_shift($args);
086                $select->where($where, $where_args);
087            }
088        }
089        return $select;
090    }
091 
092    /**
093     * Converts a camelCased word into an underscored word
094     *
095     * @param   string
096     * @return  string
097     */
098    protected function _underscore($word) {
099        $word = preg_replace('/([A-Z]+)([A-Z])/', '\1_\2', $word);
100        return strtolower(preg_replace('/([a-z])([A-Z])/', '\1_\2', $word));
101    }
102 
103    /**
104     * Converts field name to lowercase and if camelcased, converts to underscored
105     *
106     * @param string
107     * @return string
108     */
109    protected function _filterField($item) {
110        $item = $this->_underscore($item);
111        return strtolower($item);
112    }
113 
114}

Usage

I’ll use a couple of unit tests to drive the class. I’ll assume a User class extends App_Db_Table. The table schema for my test is:

1CREATE TABLE `users` (
2`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
3`username` VARCHAR( 32 ) NOT NULL ,
4`email` VARCHAR( 64 ) NOT NULL ,
5`city` VARCHAR( 32 ) NOT NULL ,
6`state` VARCHAR( 32 ) NOT NULL ,
7`favourite_colour` VARCHAR( 16 ) NOT NULL
8)

And the relevant section of the test class is as shown:

01...
02/**
03 * Add  a user to the users table
04 *
05 * @param   array   $attributes
06 */
07private function _createUser($attributes = array()) {
08    $details = array(
09        'username'  => 'john',
10        'email'     => 'john@example.com'
11        'city'      => 'Colchester',
12        'state'     => 'Essex',
13        'favourite_colour' => 'red'
14    );
15    $params = array_merge($details, $attributes);
16    $user = new User();
17    $user->createRow($params)->save();
18}
19 
20/**
21 * Test findBy Dynamic finder with one parameter
22 */
23public function testFindByWithOneParameter() {
24    $this->_createUser();
25    $finder = new User();
26 
27    $found = $finder->findByEmail('john@example.com');
28    $this->assertEquals(1, count($found));
29 
30    $found = $finder->findByEmail('smith@example.com');
31    $this->assertFalse($found);
32}
33 
34/**
35 * Test findBy Dynamic finder with underscored column names
36 */
37public function testFindByWithUnderscoredColumnNames() {
38    $this->_createUser();
39    $finder = new User();
40 
41    $found = $finder->findByFavouriteColour('red');
42    $this->assertEquals(1, count($found));
43}
44 
45/**
46 * Test findBy dynamic finder with more than one parameter
47 */
48public function testFindByWithMultipleParameters() {
49    $this->_createUser();
50    $finder = new User();
51 
52    $found = $finder->findByStateAndCity('Essex', 'Colchester');
53    $this->assertEquals('Essex', $found->state);
54 
55    $found = $finder->findByStateAndCity('Essex', 'London');
56    $this->assertFalse($found);
57 
58    $found = $finder->findByStateAndCity('London', 'Colchester');
59    $this->assertFalse($found);
60}
61 
62/**
63 * Test findAllBy Dynamic finder with one parameter
64 */
65public function testFindAllByWithOneParameter() {
66    $this->_createUser();
67    $this->_createUser(array(
68        'username'  => 'smith',
69        'email'     => 'smith@example.com'
70    ));
71 
72    $finder = new User();
73 
74    $found = $finder->findAllByState('Essex');
75    $this->assertEquals(2, count($found));
76 
77    $found = $finder->findAllByUsername('john');
78    $this->assertEquals(1, count($found));
79 
80    $found = $finder->findAllByEmail('smith');
81    $this->assertEquals(0, count($found));
82}
83...

No related posts.

This entry was posted in Zend Framework. Bookmark the permalink.

3 Responses to Zend_Db_Table dynamic finders

baphled says:

June 17, 2009 at 10:52 pm

Nice write up, am hoping this functionality will be in Jara

Reply
Jérôme says:

September 2, 2009 at 5:51 pm

Create job, thank you.

Reply
tommy says:

May 5, 2010 at 4:21 pm

Just wanted to say I really liked the site. You have really put a lot of time into your posts and it is just great!

Reply

Leave a Reply Cancel reply