Zend_Db_Table dynamic finders

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.

<?php

abstract class App_Db_Table extends Zend_Db_Table_Abstract {

	/**
	 * Db Instance
	 */
	protected $_db;

	/**
	 * Call method used to implement the dynamic finders
	 *
	 * @param string
	 * @param array
	 * @return function || void
	 */
	public function __call($method, $args) {
		$watch = array('findBy','findAllBy');
		foreach ($watch as $found) {
			if (stristr($method, $found)) {
				$fields = str_replace($found, '', $method);
				return $this->{'_' . $found}($fields, $args);
			}
		}
		throw new Exception("Call to undefined method App_Db_Table::{$method}()");
	}

	/**
     * Initialize object
     *
     * Called from {@link __construct()} as final step of object instantiation.
     */
    public function init() {
    	$this->_db = Zend_Registry::get('db');
    }

	/**
	 * Find By
	 *
	 * This method only ever returns the first record it finds!
	 *
	 * @param 	string
	 * @param 	array
	 * @return  object|false
	 */
	protected function _findBy($columns, $args) {
		$stmt = $this->_buildQuery($columns, $args);
		return $this->fetchRow($stmt);
	}

	/**
	 * Find All By
	 *
	 * @param 	string
	 * @param 	array
	 * @return	 object|false
	 */
	protected function _findAllBy($columns, $args) {
		$stmt = $this->_buildQuery($columns, $args);
		return $this->fetchAll($stmt);
	}

	/**
	 * Builds the query for the findBy methods
	 *
	 * @param 	string
	 * @param 	array
	 * @return 	Zend_Db_Select
	 */
	protected function _buildQuery($columns, $args) {
		$fields = explode('And', $columns);
		$count = count($fields);

		$where = "{$this->_filterField($fields[0])} = ?";
		$where_args = $args[0];
		unset($args[0]);

		$select = $this->select();
		$select->where($where, $where_args);

		if ($count > 1) {
			array_shift($fields);
			foreach ($fields as $field) {
				$where = "{$this->_filterField($field)} = ?";
				$where_args = array_shift($args);
				$select->where($where, $where_args);
			}
		}
		return $select;
	}

	/**
	 * Converts a camelCased word into an underscored word
	 *
	 * @param 	string
	 * @return  string
	 */
    protected function _underscore($word) {
    	$word = preg_replace('/([A-Z]+)([A-Z])/', '\1_\2', $word);
		return strtolower(preg_replace('/([a-z])([A-Z])/', '\1_\2', $word));
    }

    /**
     * Converts field name to lowercase and if camelcased, converts to underscored
     *
     * @param string
     * @return string
     */
    protected function _filterField($item) {
    	$item = $this->_underscore($item);
    	return strtolower($item);
    }

}

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:

CREATE TABLE `users` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 32 ) NOT NULL ,
`email` VARCHAR( 64 ) NOT NULL ,
`city` VARCHAR( 32 ) NOT NULL ,
`state` VARCHAR( 32 ) NOT NULL ,
`favourite_colour` VARCHAR( 16 ) NOT NULL
)

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

...
/**
 * Add  a user to the users table
 *
 * @param   array   $attributes
 */
private function _createUser($attributes = array()) {
    $details = array(
        'username'  => 'john',
        'email'     => 'john@example.com'
        'city'      => 'Colchester',
        'state'     => 'Essex',
        'favourite_colour' => 'red'
    );
    $params = array_merge($details, $attributes);
    $user = new User();
    $user->createRow($params)->save();
}

/**
 * Test findBy Dynamic finder with one parameter
 */
public function testFindByWithOneParameter() {
	$this->_createUser();
	$finder = new User();

	$found = $finder->findByEmail('john@example.com');
	$this->assertEquals(1, count($found));

	$found = $finder->findByEmail('smith@example.com');
	$this->assertFalse($found);
}

/**
 * Test findBy Dynamic finder with underscored column names
 */
public function testFindByWithUnderscoredColumnNames() {
	$this->_createUser();
	$finder = new User();

	$found = $finder->findByFavouriteColour('red');
	$this->assertEquals(1, count($found));
}

/**
 * Test findBy dynamic finder with more than one parameter
 */
public function testFindByWithMultipleParameters() {
	$this->_createUser();
	$finder = new User();

	$found = $finder->findByStateAndCity('Essex', 'Colchester');
	$this->assertEquals('Essex', $found->state);

	$found = $finder->findByStateAndCity('Essex', 'London');
	$this->assertFalse($found);

	$found = $finder->findByStateAndCity('London', 'Colchester');
	$this->assertFalse($found);
}

/**
 * Test findAllBy Dynamic finder with one parameter
 */
public function testFindAllByWithOneParameter() {
	$this->_createUser();
	$this->_createUser(array(
        'username'  => 'smith',
        'email'     => 'smith@example.com'
    ));

    $finder = new User();

	$found = $finder->findAllByState('Essex');
	$this->assertEquals(2, count($found));

	$found = $finder->findAllByUsername('john');
	$this->assertEquals(1, count($found));

	$found = $finder->findAllByEmail('smith');
	$this->assertEquals(0, count($found));
}
...

No related posts.