Re: Ideas for lightweight ORM implementation
Posted: Wed Nov 18, 2009 4:31 pm
For the collection classes, it meant I no longer had to keep track of my joins and bound parameters myself. I was having to do that in order to pass valid prepared queries to my PDOResultIterator. It meant when writing List classes, I had to be careful to keep the bound parameters in the same order I was adding the options.
Now, with using ZendDB, I can add these things ad hoc to a Zend_DB_Select object, and let it handle keeping everything straight. This is, of course on top of letting Zend_Db handle the variations in the databases, which was the big reason I tried this out.
Original UserList using PDO.
New UserList using Zend_Db
Now, with using ZendDB, I can add these things ad hoc to a Zend_DB_Select object, and let it handle keeping everything straight. This is, of course on top of letting Zend_Db handle the variations in the databases, which was the big reason I tried this out.
Original UserList using PDO.
Code: Select all
/**
* A collection class for User objects
*
* This class creates a select statement, only selecting the ID from each row
* PDOResultIterator handles iterating and paginating those results.
* As the results are iterated over, PDOResultIterator will pass each desired
* ID back to this class's loadResult() which will be responsible for hydrating
* each User object
*
* Beyond the basic $fields handled, you will need to write your own handling
* of whatever extra $fields you need
*
* The PDOResultIterator uses prepared queries; it is recommended to use bound
* parameters for each of the options you handle
*
* @copyright 2006-2008 City of Bloomington, Indiana
* @license http://www.gnu.org/copyleft/gpl.html GNU/GPL, see LICENSE.txt
* @author Cliff Ingham <inghamn@bloomington.in.gov>
*/
class UserList extends PDOResultIterator
{
/**
* @param array $fields
*/
public function __construct($fields=null)
{
$this->select = 'select users.id as id from users';
if (is_array($fields)) $this->find($fields);
}
/**
* @param array $fields
* @param string $sort
* @param string $limit
* @param string $groupBy
*/
public function find($fields=null,$sort='username',$limit=null,$groupBy=null)
{
$this->sort = $sort;
$this->limit = $limit;
$this->groupBy = $groupBy;
$this->joins = '';
$options = array();
$parameters = array();
if (isset($fields['id'])) {
$options[] = 'id=:id';
$parameters[':id'] = $fields['id'];
}
if (isset($fields['username'])) {
$options[] = 'username=:username';
$parameters[':username'] = $fields['username'];
}
if (isset($fields['password'])) {
$options[] = 'password=:password';
$parameters[':password'] = $fields['password'];
}
if (isset($fields['authenticationMethod'])) {
$options[] = 'authenticationMethod=:authenticationMethod';
$parameters[':authenticationMethod'] = $fields['authenticationMethod'];
}
// Finding on fields from other tables required joining those tables.
// You can add fields from other tables to $options by adding the join SQL
// to $this->joins here
$joins = array();
if (isset($fields['firstname'])) {
$joins['peopleJoin'] = 'left join people on users.id=people.user_id';
$options[] = 'firstname=:firstname';
$parameters[':firstname'] = $fields['firstname'];
}
if (isset($fields['lastname'])) {
$joins['peopleJoin'] = 'left join people on users.id=people.user_id';
$options[] = 'lastname=:lastname';
$parameters[':lastname'] = $fields['lastname'];
}
if (isset($fields['email'])) {
$joins['peopleJoin'] = 'left join people on users.id=people.user_id';
$options[] = 'email=:email';
$parameters[':email'] = $fields['email'];
}
if (isset($fields['role'])) {
$joins['roleJoin'] = 'left join user_roles on users.id=user_id left join roles on role_id=roles.id';
$options[] = 'role=:role';
$parameters[':role'] = $fields['role'];
}
$this->joins = implode(' ',$joins);
$this->populateList($options,$parameters);
}
/**
* @param mixed $key
*/
protected function loadResult($key)
{
return new User($this->list[$key]);
}
}
Code: Select all
/**
* A collection class for User objects
*
* This class creates a zend_db select statement.
* ZendDbResultIterator handles iterating and paginating those results.
* As the results are iterated over, ZendDbResultIterator will pass each desired
* row back to this class's loadResult() which will be responsible for hydrating
* each User object
*
* Beyond the basic $fields handled, you will need to write your own handling
* of whatever extra $fields you need
*
* @copyright 2009 City of Bloomington, Indiana
* @license http://www.gnu.org/copyleft/gpl.html GNU/GPL, see LICENSE.txt
* @author Cliff Ingham <inghamn@bloomington.in.gov>
*/
class UserList extends ZendDbResultIterator
{
private $columns = array('id','person_id','username','password','authenticationmethod');
/**
* Creates a basic select statement for the collection.
*
* Populates the collection if you pass in $fields
* Setting itemsPerPage turns on pagination mode
* In pagination mode, this will only load the results for one page
*
* @param array $fields
* @param int $itemsPerPage Turns on Pagination
* @param int $currentPage
*/
public function __construct($fields=null,$itemsPerPage=null,$currentPage=null)
{
parent::__construct($itemsPerPage,$currentPage);
if (is_array($fields)) {
$this->find($fields);
}
}
/**
* Populates the collection
*
* @param array $fields
* @param string|array $order Multi-column sort should be given as an array
* @param int $limit
* @param string|array $groupBy Multi-column group by should be given as an array
*/
public function find($fields=null,$order='username',$limit=null,$groupBy=null)
{
$this->select->from(array('u'=>'users'));
// Finding on fields from the Users table is handled here
if (count($fields)) {
foreach ($fields as $key=>$value) {
if (in_array($key,$this->columns)) {
$this->select->where("u.$key=?",$value);
}
}
}
// Finding on fields from other tables requires joining those tables.
// You can handle fields from other tables by adding the joins here
// If you add more joins you probably want to make sure that the
// above foreach only handles fields from the users table.
$joins = array();
// Firstname, lastname, and email come from the People table
if (isset($fields['firstname'])) {
$joins['p'] = array('table'=>'people','condition'=>'u.id=p.user_id');
$this->select->where('p.firstname=?',$fields['firstname']);
}
if (isset($fields['lastname'])) {
$joins['p'] = array('table'=>'people','condition'=>'u.id=p.user_id');
$this->select->where('p.lastname=?',$fields['lastname']);
}
if (isset($fields['email'])) {
$joins['p'] = array('table'=>'people','condition'=>'u.id=p.user_id');
$this->select->where('p.email=?',$fields['email']);
}
// To get the Role, we have to join the user_roles and roles tables
if (isset($fields['role'])) {
$joins['ur'] = array('table'=>'user_roles','condition'=>'u.id=ur.user_id');
$joins['r'] = array('table'=>'roles','condition'=>'ur.role_id=r.id');
$this->select->where('r.name=?',$fields['role']);
}
// Add all the joins we've created to the select
foreach ($joins as $key=>$join) {
$this->select->joinLeft(array($key=>$join['table']),$join['condition']);
}
$this->select->order($order);
if ($limit) {
$this->select->limit($limit);
}
if ($groupBy) {
$this->select->group($groupBy);
}
$this->populateList();
}
/**
* Hydrates all the objects from a database result set
*
* This is a callback function, called from ZendDbResultIterator. It is
* called once per row of the result.
*
* @param int $key The index of the result row to load
* @return User
*/
protected function loadResult($key)
{
return new User($this->result[$key]);
}
}