My PDOResultIterator is very similar, just tailored for my environment. One thing I'd love to figure out is a way to do prepared statements using it. The biggest difference is I go ahead and populate the internal array all at once from the very start, rather than trying to muck around with cursors.
I ended up building a PDO Iterator as well for my framework stuff. However, I built it to work in hand with the ActiveRecord style models I'm using. Before I clutter this up with code, some info on the models. The ActiveRecord style I went with means I also need Collection classes to handle the aggregrating a bunch of ActiveRecord objects. It allowed for code that seemed to make the most sense, to me at least.
Code: Select all
$user = new User(32);
$user->setEmail($somestring);
$user->save();
$users = new UserList(array('department_id'=>$deptID));
foreach($users as $user)
{
echo $user->getUsername();
}
To me, it made sense to use a collection type object to get a bunch of single users. Rather than using the User class to look up multiple users. What this means is I needed a PDOResultIterator that the collection classes could use.
The PDOResultIterator assembles the query, handles the iterating, and caches results. But calls a callback function for hydrating each row of the results. The PDOResultIterator calls the loadResult() function with the Index of the internal array that contains the result.
The collection classes are responsible for providing their version of the callback. 99% of the time, the collection classes just set up to select ID fields from the database. Then, their callback function takes each row from PDOResultIterator and returns a new object from that ID.
The PDOResultIterator takes this object, caches it in the array and returns it.
Code: Select all
abstract class PDOResultIterator implements ArrayAccess,SeekableIterator,Countable
{
protected $select = "";
protected $joins = "";
protected $where = "";
protected $sort = "";
protected $limit = "";
protected $groupBy = "";
private $sql;
protected $list = array();
private $valid = false;
private $cacheEnabled = true;
private $cache = array();
private $key;
protected function populateList(array $options=null)
{
$PDO = Database::getConnection();
# Make sure to clear out any previous list that was created
$this->list = array();
if (count($options)) { $this->where = ' where '.implode(' and ',$options); }
$orderBy = $this->sort ? "order by {$this->sort}" : '';
$groupBy = $this->groupBy ? "group by {$this->groupBy}" : '';
$limit = $this->limit ? 'limit '.$this->limit : '';
$this->sql = "{$this->select} {$this->joins} {$this->where} $groupBy $orderBy $limit";
$result = $PDO->query($this->sql);
if ($result)
{
foreach($result as $row) { $this->list[] = $row['id']; }
}
else { $e = $PDO->errorInfo(); throw new Exception($this->sql.$e[2]); }
}
public function getPagination($pageSize) { return new Paginator($this,$pageSize); }
abstract public function find($fields=null,$sort='',$limit=null,$groupBy=null);
abstract protected function loadResult($key);
# Array Access section
public function offsetExists($offset) { return array_key_exists($offset,$this->list); }
public function offsetSet($offset,$value) { } # Read-only for now
public function offsetUnset($offset) { } # Read-only for now
public function offsetGet($offset)
{
if ($this->offsetExists($offset))
{
if ($this->cacheEnabled)
{
if (!isset($this->cache[$offset])) { $this->cache[$offset] = $this->loadResult($offset); }
return $this->cache[$offset];
}
else { return $this->loadResult($offset); }
}
else { throw new OutOfBoundsException('Invalid seek position'); }
}
# Iterator Section
public function rewind() { $this->key = 0; }
public function next() { $this->key++; }
public function key() { return $this->key; }
public function valid() { return array_key_exists($this->key,$this->list); }
public function current()
{
if ($this->cacheEnabled)
{
if (!isset($this->cache[$this->key])) { $this->cache[$this->key] = $this->loadResult($this->key); }
return $this->cache[$this->key];
}
else { return $this->loadResult($this->key); }
}
public function seek($index)
{
if (isset($this->list[$index])) { $this->key = $index; }
else { throw new OutOfBoundsException('Invalid seek position'); }
}
public function getIterator() { return $this; }
# Countable Section
public function count() { return count($this->list); }
# Getters
public function getSelect() { return $this->select; }
public function getJoins() { return $this->joins; }
public function getWhere() { return $this->where; }
public function getGroupBy() { return $this->groupBy; } # IUB Libraries - Added getter for groupBy
public function getSort() { return $this->sort; }
public function getLimit() { return $this->limit; }
public function getSQL() { return $this->sql; }
# Cache Enable/Disable functions
public function enableCache() { $this->cacheEnabled = true; }
public function disableCache() { $this->cacheEnabled = false; }
}
And here's an example UserList collection class utilizing the PDOResultIterator. (The callback function loadResult() is down at the bottom)
Code: Select all
class UserList extends PDOResultIterator
{
public function __construct($fields=null)
{
$this->select = "select distinct users.id from users";
if (is_array($fields)) $this->find($fields);
}
public function find($fields=null,$sort="username",$limit=null,$groupBy=null)
{
$this->sort = $sort;
$this->limit = $limit;
$this->groupBy = $groupBy;
$options = array();
if (isset($fields['id'])) { $options[] = "id='$fields[id]'"; }
if (isset($fields['username'])) { $options[] = "username='$fields[username]'"; }
if (isset($fields['password'])) { $options[] = "password='$fields[password]'"; }
if (isset($fields['authenticationMethod'])) { $options[] = "authenticationMethod='$fields[authenticationMethod]'"; }
if (isset($fields['firstname'])) { $options[] = "firstname='$fields[firstname]'"; }
if (isset($fields['lastname'])) { $options[] = "lastname='$fields[lastname]'"; }
if (isset($fields['department_id'])) { $options[] = "department_id='$fields[department_id]'"; }
# To search on Role, you have to join the userRoles table
if (isset($fields['role']))
{
$this->joins.= ' left join user_roles on users.id=roles.user_id left join roles on role_id=roles.id';
$options[] = "role='$fields[role]'";
}
if (isset($fields['plan_id']))
{
$this->joins.= ' left join plan_users on users.id=plan_users.user_id';
$options[] = "plan_id='$fields[plan_id]'";
}
$this->populateList($options);
}
protected function loadResult($key) { return new User($this->list[$key]); }
}