#917: Offset Oracle and Hydrator
--------------------------+------------------------------------------------ -
Reporter: Garfield-fr | Owner: romanb
Type: defect | Status: new
Priority: major | Milestone: 0.10.5
Component: Attributes | Version: 0.10
Resolution: | Keywords:
--------------------------+------------------------------------------------ -
Comment (by bruno.p.reis):
Since in oracle the rownum is mandatory, I did the following to solve
this:
(i think it would be better changing the Connection::modifyLimitQuery
method, but this explains what is needed...)
{{{
class Doctrine_Connection_Oracle extends Doctrine_Connection
{
protected $pkNames;
// ....
public function setPkNames($pkNames) {
$this->pkNames = $pkNames;
}
// ....
public function modifyLimitQuery($query, $limit = false, $offset =
false, $isManip = false)
{
$limit = (int) $limit;
$offset = (int) $offset;
if (preg_match('/^\s*SELECT/i', $query)) {
if ( ! preg_match('/\sFROM\s/i', $query)) {
$query .= " FROM dual";
}
if ($limit > 0) {
// taken from
http://svn.ez.no/svn/ezcomponents/packages/Database
$max = $offset + $limit;
### Have distinct behavior for distinct querys (joins,
etc...) and normal pagination...
$pk = $this->pkNames?
$this->quoteIdentifier($this->pkNames) :
'*';
if ($offset > 0) {
$min = $offset + 1;
$query = 'SELECT '.$pk.' FROM (SELECT a.*, ROWNUM
dctrn_rownum FROM (' . $query
. ') a WHERE ROWNUM <= ' . $max . ') WHERE
dctrn_rownum >= ' . $min;
} else {
$query = 'SELECT '.$pk.' FROM (' . $query .') a WHERE
ROWNUM <= ' . $max;
}
}
}
return $query;
}
}}}
and...
{{{
class Doctrine_Query extends Doctrine_Query_Abstract implements Countable,
Serializable
{
// .....
public function getLimitSubquery()
{
$map = reset($this->_queryComponents);
$table = $map['table'];
$componentAlias = key($this->_queryComponents);
// get short alias
$alias = $this->getTableAlias($componentAlias);
// what about composite keys?
$primaryKey = $alias . '.' .
$table->getColumnName($table->getIdentifier());
// initialize the base of the subquery
$subquery = 'SELECT DISTINCT ' .
$this->_conn->quoteIdentifier($primaryKey);
$driverName =
$this->_conn->getAttribute(Doctrine::ATTR_DRIVER_NAME);
#### added this so the oracle_connection knows what to look for in
the select
if ($driverName == 'oci') {
if( count($this->_sqlParts['orderby']) > 0 ) {
$subquery .= ','.implode(', ', $this->_sqlParts['orderby']);
}
$this->_conn->setPkNames($table->getColumnName($table->getIdentifier()));
}
}}}
AND then I had to change the Hydrator because of the DCTRN_ROWNUM field
when the select still as Select * from...
{{{
protected function _gatherRowData(&$data, &$cache, &$id,
&$nonemptyComponents)
{
$rowData = array();
foreach ($data as $key => $value) {
// Parse each column name only once. Cache the results.
if ( ! isset($cache[$key])) {
if($key=='DCTRN_ROWNUM') continue;
}}}
--
Ticket URL: <http://trac.phpdoctrine.org/ticket/917#comment:4>
Doctrine <http://www.phpdoctrine.org>
PHP Doctrine Object Relational Mapper
